package com.xoa.service.schoolHealthy.impl;

import com.xoa.dao.department.DepartmentMapper;
import com.xoa.dao.schoolHealthy.RepHealthyInfoMapper;
import com.xoa.dao.schoolHealthy.RepMembersMapper;
import com.xoa.model.schoolHealthy.RepHealthyInfo;
import com.xoa.model.schoolHealthy.RepHealthyInfoWithBLOBs;
import com.xoa.model.schoolHealthy.RepMembers;
import com.xoa.model.users.Users;
import com.xoa.service.easyReport.RepStatisticsService;
import com.xoa.service.schoolHealthy.HealthyInfoService;
import com.xoa.util.*;
import com.xoa.util.common.StringUtils;
import com.xoa.util.common.log.FileUtils;
import com.xoa.util.common.session.SessionUtils;
import com.xoa.util.dataSource.ContextHolder;
import com.xoa.util.page.PageParams;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
public class HealthyInfoServiceImpl implements HealthyInfoService {


    @Resource
    RepHealthyInfoMapper repHealthyInfoMapper;

    @Resource
    RepMembersMapper repMembersMapper;

    @Resource
    DepartmentMapper departmentMapper;

    @Override
    public ToJson<RepHealthyInfoWithBLOBs> insertHealthyInfo(HttpServletRequest request , RepHealthyInfoWithBLOBs repHealthyInfoWithBLOBs) {
        Users users= SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users());
        ToJson<RepHealthyInfoWithBLOBs> toJson = new ToJson<>(1,"err");
        try {
            //判断是编辑还是新增
            if(repHealthyInfoWithBLOBs.getHealId() != null){
                int i = repHealthyInfoMapper.updateByPrimaryKeySelective(repHealthyInfoWithBLOBs);
                if(i>0){
                    toJson.setFlag(0);
                    toJson.setMsg("true");
                }
            }else{
                //1.根据填报姓名和手机号和时间确定当天是否有已经填报过的
                Map map = new HashMap();
                map.put("userName",repHealthyInfoWithBLOBs.getUserName());
                map.put("phone",repHealthyInfoWithBLOBs.getPhone());
                map.put("createTime",new Date());
                repHealthyInfoWithBLOBs.setCreateTime(new Date());
                List<RepHealthyInfoWithBLOBs> list = repHealthyInfoMapper.findHealthyInfo(map);
                if(list != null && list.size()>0){
                    for(RepHealthyInfoWithBLOBs repHealthyInfoWithBLOBs1:list ){
                        repHealthyInfoWithBLOBs.setUserId(repHealthyInfoWithBLOBs1.getUserId());
                        repHealthyInfoWithBLOBs.setDeptId(repHealthyInfoWithBLOBs1.getDeptId());
                        repHealthyInfoWithBLOBs.setHealId(repHealthyInfoWithBLOBs1.getHealId());
                        int i = repHealthyInfoMapper.updateByPrimaryKeySelective(repHealthyInfoWithBLOBs);
                        if(i>0){
                            toJson.setFlag(0);
                            toJson.setMsg("true");
                        }
                    }
                }else{
                    repHealthyInfoWithBLOBs.setUserId(users.getUserId());
                    repHealthyInfoWithBLOBs.setDeptId(users.getDeptId());
                    int i = repHealthyInfoMapper.insertSelective(repHealthyInfoWithBLOBs);
                    if(i>0){
                        toJson.setFlag(0);
                        toJson.setMsg("true");
                    }
                }
            }
        }catch (Exception e){
            toJson.setMsg(e.getMessage());
        }
        return toJson;
    }

    @Override
    public ToJson<RepHealthyInfoWithBLOBs> findHealthyInfoById(HttpServletRequest request ,Integer healId) {
        String sqlType = "xoa" + (String) request.getSession().getAttribute(
                "loginDateSouse");
        ContextHolder.setConsumerType(sqlType);
        ToJson<RepHealthyInfoWithBLOBs> toJson = new ToJson<>(1,"err");
        try{
            RepHealthyInfoWithBLOBs repHealthyInfoWithBLOBs = repHealthyInfoMapper.selectByPrimaryKey(healId);
            if (repHealthyInfoWithBLOBs.getAttachmentName() != null && repHealthyInfoWithBLOBs.getAttachmentId() != null) {
                repHealthyInfoWithBLOBs.setAttachment(GetAttachmentListUtil.returnAttachment(repHealthyInfoWithBLOBs.getAttachmentName(), repHealthyInfoWithBLOBs.getAttachmentId(), sqlType, GetAttachmentListUtil.HEALTHY_INFO));
            } else {
                repHealthyInfoWithBLOBs.setAttachmentName("");
                repHealthyInfoWithBLOBs.setAttachmentId("");
            }
            List<RepMembers> repMembers = repMembersMapper.selectByHealId(healId);
            repHealthyInfoWithBLOBs.setMembersList(repMembers);
            toJson.setObject(repHealthyInfoWithBLOBs);
            toJson.setFlag(0);
            toJson.setMsg("true");
        }catch (Exception e){
            toJson.setMsg(e.getMessage());
        }
        return toJson;
    }

    @Override
    public ToJson<RepHealthyInfoWithBLOBs> updateHealthyInfo(RepHealthyInfoWithBLOBs repHealthyInfoWithBLOBs) {
        ToJson<RepHealthyInfoWithBLOBs> toJson = new ToJson<>(1,"err");
        int i = repHealthyInfoMapper.updateByPrimaryKeySelective(repHealthyInfoWithBLOBs);
        if(i>0){
            List<RepMembers> membersList = repHealthyInfoWithBLOBs.getMembersList();
            for (RepMembers repMembers:membersList ) {
                repMembers.setHealId(repHealthyInfoWithBLOBs.getHealId());
                if(repMembers.getMemdId()!= null){
                    repMembersMapper.updateByPrimaryKeySelective(repMembers);
                }else{
                    repMembersMapper.insertSelective(repMembers);
                }
            }
            toJson.setFlag(0);
            toJson.setMsg("true");
        }
        return toJson;
    }

    @Override
    public ToJson<RepHealthyInfoWithBLOBs> deleteHealthyInfo(Integer healId) {
        ToJson<RepHealthyInfoWithBLOBs> toJson = new ToJson<>(1,"err");
        try{
            //根据姓名和手机号查询所有记录 并删除
            RepHealthyInfoWithBLOBs repHealthyInfoWithBLOBs = repHealthyInfoMapper.selectByPrimaryKey(healId);
            Map map = new HashMap();
//            map.put("identityNo",repHealthyInfoWithBLOBs.getIdentityNo());
            map.put("userName",repHealthyInfoWithBLOBs.getUserName());
            map.put("phone",repHealthyInfoWithBLOBs.getPhone());
            //删除时需要查询全部的数据，包含历史数据
            List<RepHealthyInfoWithBLOBs> list = repHealthyInfoMapper.findHealthyInfos(map);
            for(RepHealthyInfoWithBLOBs repHealthyInfoWithBLOBs1 :list){
                int i = repHealthyInfoMapper.deleteByPrimaryKey(repHealthyInfoWithBLOBs1.getHealId());
                if(i>0){
                    repMembersMapper.deleteByHealId(repHealthyInfoWithBLOBs1.getHealId());
                }
            }
            toJson.setFlag(0);
            toJson.setMsg("true");
        }catch (Exception e){
            toJson.setMsg(e.getMessage());
        }
        return toJson;
    }

    @Override
    public ToJson<RepHealthyInfoWithBLOBs> findHealthyInfo(HttpServletRequest request ,String keyWork,String schoolManageType,String createTime,RepHealthyInfoWithBLOBs repHealthyInfoWithBLOBs,Integer limit,Integer page) {
        String sqlType = "xoa" + (String) request.getSession().getAttribute(
                "loginDateSouse");
        ContextHolder.setConsumerType(sqlType);
        Cookie redisSessionCookie = CookiesUtil.getCookieByName(request, "redisSessionId");
        Users users = SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users(),redisSessionCookie);
        ToJson<RepHealthyInfoWithBLOBs> toJson = new ToJson<>(1,"err");
        try{
            Map map = BeanUtils.describe(repHealthyInfoWithBLOBs);
            PageParams pageParams = new PageParams();
            pageParams.setUseFlag(true);
            pageParams.setPageSize(limit);
            pageParams.setPage(page);
            map.put("page",pageParams);
            map.put("keyWork",keyWork);
            map.put("schoolManageType",schoolManageType);
            map.put("createTime",createTime);
            map.put("returnTime",repHealthyInfoWithBLOBs.getReturnTime());
            map.put("expectReturnTime",repHealthyInfoWithBLOBs.getExpectReturnTime());
            Map<String, Object> deptMapById = departmentMapper.getDeptMapById(users.getDeptId());
            String organNum = deptMapById.get("ORGAN_NUM").toString();
            if(StringUtils.checkNull(organNum) || organNum.indexOf("3101129000" )== -1 ){
                map.put("deptId",users.getDeptId());
            }
            List<RepHealthyInfoWithBLOBs> list = repHealthyInfoMapper.findHealthyInfo(map);
            for(RepHealthyInfoWithBLOBs repHealthyInfo :list){
                if (repHealthyInfo.getAttachmentName() != null && repHealthyInfo.getAttachmentId() != null) {
                    repHealthyInfo.setAttachment(GetAttachmentListUtil.returnAttachment(repHealthyInfo.getAttachmentName(), repHealthyInfo.getAttachmentId(), sqlType, GetAttachmentListUtil.HEALTHY_INFO));
                } else {
                    repHealthyInfo.setAttachmentName("");
                    repHealthyInfo.setAttachmentId("");
                }
            }
            toJson.setFlag(0);
            toJson.setMsg("true");
            toJson.setTotleNum(pageParams.getTotal());
            toJson.setObj(list);
        }catch (Exception e){
            toJson.setMsg(e.getMessage());
        }
        return toJson;
    }

    @Override
    public ToJson<RepHealthyInfoWithBLOBs> getCondition(HttpServletRequest request, String type, RepHealthyInfoWithBLOBs repHealthyInfoWithBLOBs ,boolean bol) {
        String sqlType = "xoa" + (String) request.getSession().getAttribute("loginDateSouse");
        ContextHolder.setConsumerType(sqlType);
        Cookie redisSessionCookie = CookiesUtil.getCookieByName(request, "redisSessionId");
        Users users = SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users(),redisSessionCookie);
        ToJson<RepHealthyInfoWithBLOBs> toJson = new ToJson<>(1,"err");
        try{
            Map map = BeanUtils.describe(repHealthyInfoWithBLOBs);
            Map<String, Object> deptMapById = departmentMapper.getDeptMapById(users.getDeptId());
            String organNum = deptMapById.get("ORGAN_NUM").toString();
            if(StringUtils.checkNull(organNum) ||  organNum.indexOf("3101129000" )== -1 || bol){
                map.put("deptId",users.getDeptId());
            }
            //多隔离情况
            if( map.get("isolation") != null && !StringUtils.checkNull(map.get("isolation").toString())){
                String[] split = map.get("isolation").toString().split(",");
                map.put("isolation",split);
            }
            //多状态
            if(map.get("dayStatus") != null && !StringUtils.checkNull(map.get("dayStatus").toString())){
                String[] split = map.get("dayStatus").toString().split(",");
                map.put("dayStatus",split);
            }
            //多国籍
            if(map.get("nationality") != null && !StringUtils.checkNull(map.get("nationality").toString())){
                String[] split = map.get("nationality").toString().split(",");
                map.put("nationality",split);
            }
            map.put("type",type);
            map.put("createTime",repHealthyInfoWithBLOBs.getCreateTime());
            List<RepHealthyInfoWithBLOBs> list = repHealthyInfoMapper.selectCondition(map);
            for(RepHealthyInfoWithBLOBs repHealthyInfo :list){
                if (repHealthyInfo.getAttachmentName() != null && repHealthyInfo.getAttachmentId() != null) {
                    repHealthyInfo.setAttachment(GetAttachmentListUtil.returnAttachment(repHealthyInfo.getAttachmentName(), repHealthyInfo.getAttachmentId(), sqlType, GetAttachmentListUtil.HEALTHY_INFO));
                } else {
                    repHealthyInfo.setAttachmentName("");
                    repHealthyInfo.setAttachmentId("");
                }
            }
            toJson.setFlag(0);
            toJson.setMsg("true");
            toJson.setObj(list);
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.getMessage());
        }
        return toJson;
    }

    @Override
    public ToJson<RepHealthyInfoWithBLOBs> statisticsInfoType(HttpServletRequest request ,HttpServletResponse response,String schoolType,String createTime,String schoolManageType,String statePrivateId ,String liveStreet,String groupBy,String schoolName,boolean isExport) {
        ToJson<RepHealthyInfoWithBLOBs> toJson = new ToJson<>(1,"err");
        Cookie redisSessionCookie = CookiesUtil.getCookieByName(request, "redisSessionId");
        Users users = SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users(),redisSessionCookie);
        try{
            Map<String, Object> map = new HashMap<>();
            Map<String, Object> deptMapById = departmentMapper.getDeptMapById(users.getDeptId());
            String organNum = deptMapById.get("ORGAN_NUM").toString();
            if(StringUtils.checkNull(organNum) ||  organNum.indexOf("3101129000" )== -1 ){
                map.put("deptId",users.getDeptId());
            }
            boolean bol = StringUtils.checkNull(liveStreet) ? false : true;
            map.put("groupBy",groupBy);
            map.put("schoolType",schoolType);
            map.put("schoolManageType",schoolManageType);
            map.put("statePrivateId",statePrivateId);
            map.put("liveStreet",liveStreet);
            map.put("createTime",createTime);
            map.put("schoolName",schoolName);
            List<Map<String,String>> mapList = repHealthyInfoMapper.selectByMap(map);
            //按照部门查询的时候增加所有未填报信息的学校
            if(groupBy.indexOf("dep.DEPT_ID") != -1 && !bol){
                List<Map<String, String>> list = repHealthyInfoMapper.getDeptNotAdd(map);
                if(list != null ){
                    for(Map<String, String> map1 :list){
                        map1.put("IDENTITY_TYPE","1");
                        map1.put("LIVE_STREET","16");
                        map1.put("SCHOOL_NAME",map1.get("DEPT_NAME"));
                        mapList.add(map1);
                    }
                }
            }
            //记录上次的 添加记录
            Map oldMap = new HashMap();
            Integer identityId = 0;
            if(mapList != null ){
                int size = mapList.size();
                //如果最后一条数据为学生的话需要特殊处理
                if(size >= 1){
                    boolean isOneStu = "1".equals(mapList.get(size-1).get("IDENTITY_TYPE")) ? true: false;
                    for(int i = 0 ; i <size ;i++){
                        Map mapp = mapList.get(i);
                        Integer newIdentityId = Integer.valueOf(mapp.get("IDENTITY_TYPE").toString());
                        boolean bbol = true;
                        //1.如果相邻两次的查询是同样的类型说明 差某一类型的数据(补充对应的记录)
                        //2.如果相邻两次的查询是不同类型但是不是同一个学校(补充对应的记录)
                        //3.如果第一个是教职员工(补充对应的记录)
                        //4.如果最后一个是学生(补充对应的记录)
                        boolean isDept = false;
                        if(groupBy.indexOf("SCHOOL_MANAGE_TYPE") != -1){
                            isDept = !mapp.get("SCHOOL_MANAGE_TYPE").equals(oldMap.get("SCHOOL_MANAGE_TYPE"));
                        }else if(groupBy.indexOf("rhi.LIVE_STREET") != -1){
                            isDept = !mapp.get("LIVE_STREET").equals(oldMap.get("LIVE_STREET"));
                        }else if(groupBy.indexOf("dep.DEPT_ID") != -1){
                            isDept = !mapp.get("DEPT_ID").equals(oldMap.get("DEPT_ID"));
                        }
                        if(identityId == newIdentityId || (i == 0 && newIdentityId == 2) ||
                                (identityId != newIdentityId && i >=1 && newIdentityId == 2 && isDept)||
                                (i+1 == size && isOneStu)){
                            Map map2 = new HashMap();
                            //针对类型 重新计算 人员总数 ，在沪人数，失联 人数
                            if(newIdentityId == 2 && (identityId == newIdentityId  || (i == 0 && newIdentityId == 2))){
                                    //学生 说明 当前队对象 和 新增的学生 同属一个机构
                                    map2.put("IDENTITY_TYPE","1");
                                    map2.put("SCHOOL_MANAGE_TYPE",mapp.get("SCHOOL_MANAGE_TYPE"));
                                    map2.put("SCHOOL_TYPE",mapp.get("SCHOOL_TYPE"));
                                    map2.put("STATE_PRIVATE_ID",mapp.get("STATE_PRIVATE_ID"));
                                    map2.put("LIVE_STREET",mapp.get("LIVE_STREET"));
                                    map2.put("DEPT_ID",mapp.get("DEPT_ID"));
                                    map2.put("SCHOOL_NAME",mapp.get("SCHOOL_NAME"));
                                    map2.put("ORGAN_FULLNAME",mapp.get("ORGAN_FULLNAME"));
                                    map2.put("ORGAN_NUM",mapp.get("ORGAN_NUM"));
                            }else if(identityId != newIdentityId && i >=1 && newIdentityId == 2 && isDept){
                                    map2.put("IDENTITY_TYPE","2");
                                    map2.put("SCHOOL_MANAGE_TYPE",oldMap.get("SCHOOL_MANAGE_TYPE"));
                                    map2.put("SCHOOL_TYPE",oldMap.get("SCHOOL_TYPE"));
                                    map2.put("STATE_PRIVATE_ID",oldMap.get("STATE_PRIVATE_ID"));
                                    map2.put("LIVE_STREET",oldMap.get("LIVE_STREET"));
                                    map2.put("DEPT_ID",oldMap.get("DEPT_ID"));
                                    map2.put("SCHOOL_NAME",oldMap.get("SCHOOL_NAME"));
                                    map2.put("ORGAN_FULLNAME",oldMap.get("ORGAN_FULLNAME"));
                                    map2.put("ORGAN_NUM",oldMap.get("ORGAN_NUM"));
                            }else if(newIdentityId ==1){
                                map2.put("IDENTITY_TYPE", "2");
                                //如果最后一条数据为学生的话需要特殊处理 机构信息使用当前学生数据的信息
                                if(i+1 == size && isOneStu && identityId != newIdentityId){
                                    map2.put("SCHOOL_MANAGE_TYPE",mapp.get("SCHOOL_MANAGE_TYPE"));
                                    map2.put("SCHOOL_TYPE",mapp.get("SCHOOL_TYPE"));
                                    map2.put("STATE_PRIVATE_ID",mapp.get("STATE_PRIVATE_ID"));
                                    map2.put("LIVE_STREET",mapp.get("LIVE_STREET"));
                                    map2.put("DEPT_ID",mapp.get("DEPT_ID"));
                                    map2.put("SCHOOL_NAME",mapp.get("SCHOOL_NAME"));
                                    map2.put("ORGAN_FULLNAME",mapp.get("ORGAN_FULLNAME"));
                                    map2.put("ORGAN_NUM",mapp.get("ORGAN_NUM"));
                                }else {
                                    //教职员工 组织机构属于上一条数据
                                    map2.put("SCHOOL_MANAGE_TYPE", oldMap.get("SCHOOL_MANAGE_TYPE"));
                                    map2.put("SCHOOL_TYPE", oldMap.get("SCHOOL_TYPE"));
                                    map2.put("STATE_PRIVATE_ID", oldMap.get("STATE_PRIVATE_ID"));
                                    map2.put("LIVE_STREET", oldMap.get("LIVE_STREET"));
                                    map2.put("DEPT_ID", oldMap.get("DEPT_ID"));
                                    map2.put("SCHOOL_NAME", oldMap.get("SCHOOL_NAME"));
                                    map2.put("ORGAN_FULLNAME", oldMap.get("ORGAN_FULLNAME"));
                                    map2.put("ORGAN_NUM", oldMap.get("ORGAN_NUM"));
                                }
                            }

                            HandleMap(groupBy,map2,map2,createTime,bol);
                            if(i+1 == size && isOneStu && identityId != newIdentityId){
                                mapList.add(++i,map2);
                            }else if (i+1 == size && isOneStu && identityId == newIdentityId){
                                mapList.add(i,map2);
                                identityId = newIdentityId == 1 ? 2:1 ;
                                oldMap = map2;
                                bbol = false;
                            }else if(identityId != newIdentityId && i >=1 && newIdentityId == 2 && isDept) {
                                mapList.add(i,map2);
                                identityId = newIdentityId ;
                                oldMap = map2;
                                bbol = false;
                            }else{
                                mapList.add(i++,map2);
                            }
                            size++;
                        }
                        if(bbol){
                            HandleMap(groupBy,mapp,mapp,createTime,bol);
                            identityId = newIdentityId;
                            oldMap = mapp;
                        }
                    }
                }
                //导出
                if(isExport){
                    SimpleDateFormat yMd = new SimpleDateFormat("yyyy/MM/dd");
                    String date = yMd.format(new Date());
                    int key = 0;
                    int x = 0;
                    String[] stateSecondTitles = null;String[] endSecondTitles = null;
                    String[] stateBeanProperty = null;String[] endBeanProperty = null;
                    //按机构查询的话
                    if(groupBy.indexOf("SCHOOL_MANAGE_TYPE") != -1){
                        stateSecondTitles = new String[]{"管理类型", "机构总数"};endSecondTitles = new String[]{"失联人数"};
                        stateBeanProperty = new String[]{"schoolManageName", "机构总数"};endBeanProperty = new String[]{"失联人数,手工填写"};
                        x = 2;key = 1;
                    }else if(groupBy.indexOf("rhi.LIVE_STREET") != -1){
                        stateSecondTitles = new String[]{"镇街"};endSecondTitles = new String[]{};
                        stateBeanProperty = new String[]{"liveStreetName"};endBeanProperty = new String[]{};
                        x = 1;key = 2;
                    }else if(groupBy.indexOf("dep.DEPT_ID") != -1){
                        stateSecondTitles = new String[]{"学校编码","学校全称"};endSecondTitles = new String[]{"失联人数","日期"};
                        stateBeanProperty = new String[]{"ORGAN_NUM","ORGAN_FULLNAME"};endBeanProperty = new String[]{"失联人数,手工填写","日期"};
                        x = 2;key = 3;
                    }
                    String[] secondTitles = {"对象类型", "当日发现确诊人数","当日发现疑似人数","当日健康异常人数","当日密切接触人数","当日隔离人员数","人员总数",
                            "在沪人数","其中在沪留校人数","当日从湖北（含途经）返沪人数","当日从其他地区返沪人数",
                            "目前居家隔离人数","目前集中隔离人数","当日解除隔离人数",
                            "当日在湖北人数","其中在武汉人员数",
                            "在其他地区人员","在境外人员","其中港澳台籍人员数",
                            "其中外籍人员数","返校途中人数"};

                    int leng = stateSecondTitles.length+secondTitles.length+endSecondTitles.length;
                    String [] stateTitles = new String[leng];
                    String [] stateBeans = new String[leng];
                    System.arraycopy(stateSecondTitles, 0, stateTitles, 0,stateSecondTitles.length);
                    System.arraycopy(secondTitles, 0, stateTitles, stateSecondTitles.length,secondTitles.length);
                    System.arraycopy(endSecondTitles, 0, stateTitles,stateSecondTitles.length+secondTitles.length,endSecondTitles.length);

                    HSSFWorkbook workbook = new HSSFWorkbook();
                    HSSFSheet sheet = workbook.createSheet("汇总信息");
                    sheet.createRow(0);
                    sheet.createRow(1);
                    HSSFCellStyle styleField = createStyle(workbook, (short)10);
                    //合并头部单元格
                    HSSFRow rowTitle = sheet.getRow(0);
                    CellRangeAddress cellRangeAddressl1 = new CellRangeAddress(0, 0, 7+x, 13+x);
                    CellRangeAddress cellRangeAddressl2 = new CellRangeAddress(0, 0, 14+x, 15+x);
                    CellRangeAddress cellRangeAddressl3 = new CellRangeAddress(0, 0, 17+x, 19+x);
//                    CellRangeAddress cellRangeAddressl4 = new CellRangeAddress(0, 0, 20+x, 21+x);
                    sheet.addMergedRegion(cellRangeAddressl1);
                    sheet.addMergedRegion(cellRangeAddressl2);
                    sheet.addMergedRegion(cellRangeAddressl3);
//                    sheet.addMergedRegion(cellRangeAddressl4);
                    HSSFCell cell1 = rowTitle.createCell(7+x);
                    cell1.setCellValue("在沪人员");cell1.setCellStyle(styleField);
                    HSSFCell cell2 = rowTitle.createCell(14+x);
                    cell2.setCellValue("在湖北");cell2.setCellStyle(styleField);
                    HSSFCell cell3 = rowTitle.createCell(16+x);
                    cell3.setCellValue("在其他地区人员（不含境外）");cell3.setCellStyle(styleField);
                    HSSFCell cell4 = rowTitle.createCell(17+x);
                    cell4.setCellValue("在境外人员（港澳台地区、国外）");cell4.setCellStyle(styleField);
                    HSSFCell cell5 = rowTitle.createCell(20+x);
                    cell5.setCellValue("返校途中");cell5.setCellStyle(styleField);
                    for (int i = 0; i < stateTitles.length; i++) {
                        HSSFRow rowField = sheet.getRow(0);
                        if(i<7+x || i>=20+x || i == 16+x){
                            CellRangeAddress cellRangeAddressle = new CellRangeAddress(0, 1, i, i);
                            sheet.addMergedRegion(cellRangeAddressle);
                        }else if(i>=7+x && i<20+x){
                            rowField = sheet.getRow(1);
                        }
                        HSSFCell cell = rowField.createCell(i);
                        cell.setCellValue(stateTitles[i]);
                        cell.setCellStyle(styleField);
                    }
                    String[] beanProperty = {"identityName","当日发现确诊人数","当日发现疑似人数","当日健康异常人数","当日密切接触人数","当日隔离人员数","人员总数",
                            "在沪人数","留校人数","去过重点地区","当日从其他地区返沪",
                            "目前在家隔离","目前集中隔离","当日接触隔离",
                            "当日在重点地区","其中在武汉人员数",
                            "当日在其他区域人数","在境外人员","其中港澳台籍人员数",
                            "其中外籍人员数","返校途中人数（当日10点前未到上海）"};

                    System.arraycopy(stateBeanProperty, 0, stateBeans, 0,stateBeanProperty.length);
                    System.arraycopy(beanProperty, 0, stateBeans, stateBeanProperty.length,beanProperty.length);
                    System.arraycopy(endBeanProperty, 0, stateBeans, stateBeanProperty.length+beanProperty.length,endBeanProperty.length);
                    Map <Integer,Integer> mapStu = new HashMap();
                    Map <Integer,Integer> mapTea = new HashMap();
                    //计算上一条数据的唯一值
                    String keyStr = null;
                    for(int i = 0;i<mapList.size();i++){
                        Map map1 = mapList.get(i);
                        //如果是按照学校导出 最后增加导出日期
                        if(key == 3)
                            map1.put("日期",date);
                        HSSFRow rowData = sheet.createRow(i + 2);
                        boolean boll = "学生".equals(map1.get("identityName")) ? true:false;
                        for(int j = 0 ;j<stateBeans.length;j++){
                            HSSFCell cellData = rowData.createCell(j);
                            switch (key){
                                case 1:
                                    if(map1.get("SCHOOL_MANAGE_TYPE").equals(keyStr) &&("schoolManageName".equals(stateBeans[j]) || "机构总数".equals(stateBeans[j]))){
                                        CellRangeAddress cellRangeAddressle = new CellRangeAddress(i+1,i+2, j, j);
                                        sheet.addMergedRegion(cellRangeAddressle);
                                    }
                                    if(j+1==stateBeans.length){ keyStr = map1.get("SCHOOL_MANAGE_TYPE").toString(); }
                                    break;
                                case 2:
                                    if(map1.get("LIVE_STREET").equals(keyStr) &&("liveStreetName".equals(stateBeans[j]))){
                                        CellRangeAddress cellRangeAddressle = new CellRangeAddress(i+1,i+2, j, j);
                                        sheet.addMergedRegion(cellRangeAddressle);
                                    }
                                    if(j+1==stateBeans.length){ keyStr = map1.get("LIVE_STREET").toString(); }
                                    break;
                                case 3:
                                    if(map1.get("DEPT_ID").toString().equals(keyStr) &&("ORGAN_FULLNAME".equals(stateBeans[j]) || "ORGAN_NUM".equals(stateBeans[j]))){
                                        CellRangeAddress cellRangeAddressle = new CellRangeAddress(i+1,i+2, j, j);
                                        sheet.addMergedRegion(cellRangeAddressle);
                                    }
                                    if(j+1==stateBeans.length){
                                        keyStr = map1.get("DEPT_ID").toString();
                                    }
                                    break;
                            }
                            if(map1.get(stateBeans[j])!= null){
                                String str = null;
                                Integer number = 0;
                                if(map1.get(stateBeans[j]) instanceof Double ){
                                    number = (int)Math.floor((Double)map1.get(stateBeans[j]));
                                }else if(map1.get(stateBeans[j]) instanceof Long){
                                    number = ((Long)map1.get(stateBeans[j])).intValue();
                                }else if(map1.get(stateBeans[j]) instanceof BigDecimal){
                                    number = ((BigDecimal)map1.get(stateBeans[j])).intValue();
                                }else{
                                    str = map1.get(stateBeans[j]).toString();
                                }
                                //汇总数据
                                if(boll){
                                    Integer num = mapStu.get(j);
                                    if(num != null){
                                        mapStu.put(j,num+number);
                                    }else{
                                        mapStu.put(j,number);
                                    }
                                }else{
                                    Integer num = mapTea.get(j);
                                    if(num != null){
                                        mapTea.put(j,num+number);
                                    }else{
                                        mapTea.put(j,number);
                                    }
                                }
                                if(str != null){
                                    cellData.setCellValue(str);
                                }else{
                                    cellData.setCellValue(number);
                                }
                            }else{
                                cellData.setCellValue(0);
                            }
                        }
                    }
                    //增加汇总统计
                    //学生
                    int stu = mapList.size()+2;
                    HSSFRow rowstu = sheet.createRow(stu);
                    for(Map.Entry<Integer,Integer> mapp :mapStu.entrySet()){
                        HSSFCell cell = rowstu.createCell(mapp.getKey());
                        cell.setCellValue(mapp.getValue());
                    }
                    //教师
                    int tea = mapList.size()+3;
                    HSSFRow rowtea = sheet.createRow(tea);
                    for(Map.Entry<Integer,Integer> mapp :mapTea.entrySet()){
                        HSSFCell cell = rowtea.createCell(mapp.getKey());
                        cell.setCellValue(mapp.getValue());
                        Integer sum = mapStu.get(mapp.getKey());
                        if(sum != null && mapp.getValue() != null){
                            mapStu.put(mapp.getKey(),sum+mapp.getValue());
                        }else if(mapp.getValue() != null){
                            mapStu.put(mapp.getKey(),mapp.getValue());
                        }
                    }
                    //总数
                    int sum = mapList.size()+4;
                    HSSFRow rowsum = sheet.createRow(sum);
                    for(Map.Entry<Integer,Integer> mapp :mapStu.entrySet()){
                        HSSFCell cell = rowsum.createCell(mapp.getKey());
                        cell.setCellValue(mapp.getValue());
                    }

                    if(key ==1){
                        rowstu.createCell(2).setCellValue("学生人数");
                        rowtea.createCell(2).setCellValue("教工人数");
                        rowsum.createCell(2).setCellValue("师生总数");
                        rowstu.createCell(0).setCellValue("合计");
                        CellRangeAddress cellRangeAddressle0 = new CellRangeAddress(stu,sum, 0, 0);
                        CellRangeAddress cellRangeAddressle1 = new CellRangeAddress(stu,sum, 1, 1);
                        sheet.addMergedRegion(cellRangeAddressle0);
                        sheet.addMergedRegion(cellRangeAddressle1);
                    }else{
                        rowstu.createCell(1).setCellValue("学生人数");
                        rowtea.createCell(1).setCellValue("教工人数");
                        rowsum.createCell(1).setCellValue("师生总数");
                        rowstu.createCell(0).setCellValue("合计");
                        CellRangeAddress cellRangeAddressle0 = new CellRangeAddress(stu,sum, 0, 0);
                        sheet.addMergedRegion(cellRangeAddressle0);
                    }

                    ServletOutputStream out = response.getOutputStream();
                    String filename = "汇总导出.xls";
                    filename = FileUtils.encodeDownloadFilename(filename, request.getHeader("user-agent"));
                    response.setContentType("application/vnd.ms-excel");
                    response.setHeader("content-disposition", "attachment;filename=" + filename);
                    workbook.write(out);
                    out.flush();
                }
            }
            toJson.setFlag(0);
            toJson.setMsg("true");
            toJson.setObject(mapList);
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.getMessage());
        }
        return toJson;
    }

    private void HandleMap(String groupBy,Map map1,Map map2 ,String createTime,boolean bol){
        Map map3 = new HashMap();
        String schoolManageType  = map2.get("SCHOOL_MANAGE_TYPE") != null ? map2.get("SCHOOL_MANAGE_TYPE").toString():null;
        String liveStreet = map2.get("LIVE_STREET") != null ? map2.get("LIVE_STREET").toString():null;
        Integer deptId = map2.get("DEPT_ID") != null ? Integer.valueOf(map2.get("DEPT_ID").toString()):null;
        map1.put("schoolTypeName",repSchoolType((String)map2.get("SCHOOL_TYPE")));
        map1.put("schoolManageName",repSchoolManageType((String)map2.get("SCHOOL_MANAGE_TYPE")));
        map1.put("statePrivateName",repStatePrivate(map2.get("STATE_PRIVATE_ID").toString()));
        map1.put("identityName","1".equals(map2.get("IDENTITY_TYPE").toString()) ? "学生" : "教职员工");
        map1.put("liveStreetName",repLiveStreet(map2.get("LIVE_STREET").toString()));
        if(groupBy.indexOf("SCHOOL_MANAGE_TYPE") != -1){
            map3 =  repHealthyInfoMapper.getPeopleNumber(schoolManageType,null,null,createTime);
        }else if(groupBy.indexOf("rhi.LIVE_STREET") != -1){
            map3 =  repHealthyInfoMapper.getliveStreetNumber(liveStreet,null,createTime);
        }else if(groupBy.indexOf("dep.DEPT_ID") != -1 && bol){
            map3 =  repHealthyInfoMapper.getliveStreetNumber(liveStreet,deptId,createTime);
        }else if (groupBy.indexOf("dep.DEPT_ID") != -1){
            map3 =  repHealthyInfoMapper.getPeopleNumber(schoolManageType,null,deptId,createTime);
        }
            map1.put("机构总数",map3.get("jg"));
            map1.put("人员总数","1".equals(map2.get("IDENTITY_TYPE").toString()) ? map3.get("student") : map3.get("teacher"));
            map1.put("在沪人数","1".equals(map2.get("IDENTITY_TYPE").toString()) ? map3.get("在沪学生") : map3.get("在沪老师"));
            map1.put("失联人数,手工填写","1".equals(map2.get("IDENTITY_TYPE").toString()) ? map3.get("studentLost") : map3.get("teacherLost"));
    }

    @Override
    public ToJson<RepHealthyInfoWithBLOBs> chartAnalysis(String type,String stateTime ,String endTime ,String fieldType,String groupBy,String schoolType, String schoolManageType, String statePrivateId,String liveStreet) {
        ToJson<RepHealthyInfoWithBLOBs> toJson = new ToJson<>(1,"err");
        Map hashMap = new HashMap();
        try{
            hashMap.put("schoolManageType",schoolManageType);
            hashMap.put("schoolType",schoolType);
            hashMap.put("statePrivateId",statePrivateId);
            hashMap.put("liveStreet",liveStreet);
            hashMap.put("fieldType",fieldType);
            hashMap.put("groupBy",groupBy);
            hashMap.put("createTime",stateTime);
            if(!"1".equals(type) && StringUtils.checkNull(fieldType)){
                List<String> days = getDays(stateTime, endTime);
                HashMap<String ,Integer []> maps = new HashMap();
                    for (int i = 0;i<days.size();i++) {
                        hashMap.put("createTime",days.get(i));
                        List<Map<String,Object>> list = repHealthyInfoMapper.chartAnalysis(hashMap);
                        for (Map map: list) {
                            if(map != null){
                                Integer [] ints = maps.get(map.get(groupBy));
                                Long a = Long.valueOf(map.get("numbers").toString());
                                if(ints != null){
                                    ints[i] =  a.intValue();
                                    maps.put(map.get(groupBy).toString(),ints);
                                }else{
                                    Integer [] intss = new Integer[days.size()];
                                    intss[i] = a.intValue();
                                    maps.put(map.get(groupBy).toString(),intss);
                                }
                            }
                        }
                    }
                toJson.setFlag(0);
                toJson.setMsg("true");
                toJson.setObj1(days);
                toJson.setObject(maps);
                return toJson;
            }else{
                List list = repHealthyInfoMapper.chartAnalysis(hashMap);
                toJson.setFlag(0);
                toJson.setMsg("true");
                toJson.setObject(list);
            }
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.getMessage());
        }
        return toJson;
    }

    public ToJson getSchool(HttpServletRequest request){
        Users user= SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users());
        ToJson toJson = new ToJson(1,"err");
        try{
            Map<String, String> fatherDept = getFatherDept(user.getDeptId());
            toJson.setFlag(0);
            toJson.setMsg("true");
            toJson.setObject(fatherDept);
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.getMessage());
        }
        return toJson;
    }

    public static List<String> getDays(String startTime, String endTime) {
            List list = new ArrayList();
            Date start = DateFormat.DateToStr(startTime);
            Date end = DateFormat.DateToStr(endTime);
            Calendar tempStart = Calendar.getInstance();
            tempStart.setTime(start);
            Calendar tempEnd = Calendar.getInstance();
            tempEnd.setTime(end);
            tempEnd.add(Calendar.DATE, +1);// 日期加1(包含结束)
            while (tempStart.before(tempEnd)) {
                list.add(DateFormat.getDatestr(tempStart.getTime()));
                tempStart.add(Calendar.DAY_OF_YEAR, 1);
            }
        return list;
    }

    /**
     * 学生信息导入
     * @param request
     * @param file
     * @return
     */
    @Override
    public ToJson<RepHealthyInfoWithBLOBs> ImportHealthyInfo(HttpServletRequest request, MultipartFile file) {
        Cookie redisSessionCookie = CookiesUtil.getCookieByName(request, "redisSessionId");
        Users users = SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users(),redisSessionCookie);
        ToJson toJson=new ToJson(1,"err");
        try{
            Map mapMsg=new HashMap();
            List listMsg=new ArrayList();
            //返回成功失败条数
            int trueNum=0;
            int falseNum=0;

            // 判断是否为空文件
            if (file.isEmpty()) {
                toJson.setMsg("请上传文件！");
                toJson.setFlag(1);
                return toJson;
            } else {
                String fileName = file.getOriginalFilename();
                if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
                    InputStream in = file.getInputStream();
                    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(in);
                    HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
                    if (hssfSheet!=null) {
                        //从第二行开始，一行一行遍历
                        for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                            HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                            HSSFCell cell1 = hssfRow.getCell(0);
                            cell1.setCellType(CellType.STRING); //统一单元格类型
                            if(StringUtils.checkNull(cell1.getStringCellValue())){
                                break;
                            }
                            RepHealthyInfoWithBLOBs repHealthyInfoWithBLOBs = new RepHealthyInfoWithBLOBs();
                            Map rowMsg =new HashMap();
                            for (int i=0;i<hssfSheet.getRow(rowNum).getPhysicalNumberOfCells();i++) {
                                HSSFCell cell = hssfRow.getCell(i);
                                cell.setCellType(CellType.STRING); //统一单元格类型
                                switch (i){
                                    case 0:
                                        //学校编码
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            repHealthyInfoWithBLOBs.setOrganNum(cell.getStringCellValue());
                                        }else{
                                            rowMsg.put("ORGAN_NUM","学校编码不能为空");
                                        }
                                        break;
                                    case 1:
                                        //学校简称
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            repHealthyInfoWithBLOBs.setSchoolName(cell.getStringCellValue());
                                        }else{
                                            rowMsg.put("SCHOOL_NAME","学校简称不能为空");
                                        }
                                        break;
                                    case 2:
                                        //身份类型 对象类型
                                        if (!StringUtils.checkNull(cell.getStringCellValue())){
                                            switch (cell.getStringCellValue()){
                                                case "学生":
                                                    repHealthyInfoWithBLOBs.setIdentityType("1");
                                                    break;
                                                case "教职员工":
                                                    repHealthyInfoWithBLOBs.setIdentityType("2");
                                                    break;
                                                default:
                                                    rowMsg.put("IDENTITY_TYPE","对象类型字段不正确");
                                                    break;
                                            }
                                        }else{
                                            rowMsg.put("IDENTITY_TYPE","对象类型不能为空");
                                        }
                                        break;
                                    case 3:
                                        //学生特有字段 学生所在年级
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            String str = null;
                                            switch (cell.getStringCellValue()) {
                                                case "托育0-3":
                                                    str = "1";
                                                    break;
                                                case "幼儿园托班":
                                                    str = "2";
                                                    break;
                                                case "小班":
                                                    str = "3";
                                                    break;
                                                case "中班":
                                                    str = "4";
                                                    break;
                                                case "大班":
                                                    str = "5";
                                                    break;
                                                case "一年级":
                                                    str = "6";
                                                    break;
                                                case "二年级":
                                                    str = "7";
                                                    break;
                                                case "三年级":
                                                    str = "8";
                                                    break;
                                                case "四年级":
                                                    str = "9";
                                                    break;
                                                case "五年级":
                                                    str = "10";
                                                    break;
                                                case "六年级":
                                                    str = "11";
                                                    break;
                                                case "七年级":
                                                    str = "12";
                                                    break;
                                                case "八年级":
                                                    str = "13";
                                                    break;
                                                case "九年级":
                                                    str = "14";
                                                    break;
                                                case "十年级":
                                                    str = "15";
                                                    break;
                                                case "十一年级":
                                                    str = "16";
                                                    break;
                                                case "十二年级":
                                                    str = "17";
                                                    break;
                                                case "中职校一年级":
                                                    str = "18";
                                                    break;
                                                case "中职校二年级":
                                                    str = "19";
                                                    break;
                                                case "中职校三年级":
                                                    str = "20";
                                                    break;
                                                case "中职校四年级":
                                                    str = "21";
                                                    break;
                                                case "成教中心社区学校老年大学":
                                                    str = "22";
                                                    break;
                                                case "全日制培训机构":
                                                    str = "23";
                                                    break;
                                            }
                                            if (str!=null && !str.equals("")){
                                                repHealthyInfoWithBLOBs.setStuGrade(str);
                                            }else{
                                                rowMsg.put("STU_GRADE","学生所在年级字段不正确");
                                            }
                                        }else{
                                            repHealthyInfoWithBLOBs.setStuGrade("");
                                        }
                                        break;
                                    case 4:
                                        //学生特有字段 学生所在班级
                                        repHealthyInfoWithBLOBs.setStuClass(cell.getStringCellValue());
                                        break;
                                    case 5:
                                        //教师特有 教职工职务
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            String str = null;
                                            switch (cell.getStringCellValue()) {
                                                case "校级领导":
                                                    str = "1";
                                                    break;
                                                case "中层干部":
                                                    str = "2";
                                                    break;
                                                case "教师":
                                                    str = "3";
                                                    break;
                                                case "外聘教师":
                                                    str = "4";
                                                    break;
                                                case "教辅人员":
                                                    str = "5";
                                                    break;
                                                case "施工人员":
                                                    str = "6";
                                                    break;
                                            }
                                            if(!StringUtils.checkNull(str)){
                                                repHealthyInfoWithBLOBs.setTeaPost(str);
                                            }else{
                                                rowMsg.put("TEA_POST","教职工职务字段不正确");

                                            }
                                        }else{
                                            repHealthyInfoWithBLOBs.setTeaPost("");
                                        }
                                        break;
                                    case 6:
                                        //姓名
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            repHealthyInfoWithBLOBs.setUserName(cell.getStringCellValue());
                                        }else{
                                            rowMsg.put("USER_NAME","姓名不能为空");
                                        }
                                        break;
                                    case 7:
                                        //性别
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {

                                            switch (cell.getStringCellValue()) {
                                                case "女":
                                                    repHealthyInfoWithBLOBs.setSex("2");
                                                    break;
                                                case "男":
                                                    repHealthyInfoWithBLOBs.setSex("1");
                                                    break;
                                                default:
                                                    rowMsg.put("SEX","性别字段不正确");
                                                    break;
                                            }
                                        }else{
                                            rowMsg.put("SEX","性别不能为空");
                                        }
                                        break;
                                    case 8:
                                        //国籍
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            repHealthyInfoWithBLOBs.setNationality(cell.getStringCellValue());
                                        }else{
                                            rowMsg.put("NATIONALITY","国籍不能为空");
                                        }
                                        break;
                                    case 9:
                                        //身份证号
                                        repHealthyInfoWithBLOBs.setIdentityNo(cell.getStringCellValue());
                                        break;
                                    case 10:
                                        //联系电话
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            repHealthyInfoWithBLOBs.setPhone(cell.getStringCellValue());
                                        }else{
                                            rowMsg.put("PHONE","手机不能为空");
                                        }
                                        break;
                                    case 11:
                                        //当日状态
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            String str = null;
                                            switch (cell.getStringCellValue()) {
                                                case "当日在沪":
                                                    str = "1";
                                                    break;
                                                case "当日在重点地区（湖北武汉）":
                                                    str = "2";
                                                    break;
                                                case "当日在重点地区（湖北其他地区）":
                                                    str = "3";
                                                    break;
                                                case "当日在国内其他地区":
                                                    str = "4";
                                                    break;
                                                case "当日在境外（含中国港澳台地区）":
                                                    str = "5";
                                                    break;
                                                case "返校途中":
                                                    str = "6";
                                                    break;
                                            }
                                            if(!StringUtils.checkNull(str)) {
                                                repHealthyInfoWithBLOBs.setDayStatus(str);
                                            }else{
                                                rowMsg.put("DAY_STATUS","当日状态字段不正确");
                                            }
                                        }else{
                                            rowMsg.put("DAY_STATUS","当日状态不能为空");
                                        }
                                        break;
                                    case 12:
                                        //留校情况
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            switch (cell.getStringCellValue()) {
                                                case "未留校":
                                                    repHealthyInfoWithBLOBs.setDetention("1");
                                                    break;
                                                case "留校":
                                                    repHealthyInfoWithBLOBs.setDetention("2");
                                                    break;
                                                default:
                                                    rowMsg.put("DETENTION","留校情况字段不正确");
                                                    break;
                                            }
                                        }else{
                                            rowMsg.put("DETENTION","留校情况不能为空");
                                        }
                                        break;
                                    case 13:
                                        // 离开湖北境内日期 允许为空
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            if (DateFormat.isLegalDate(cell.getStringCellValue())){
                                                repHealthyInfoWithBLOBs.setDepartureTime(DateFormat.getDates(cell.getStringCellValue()));
                                            }else{
                                                rowMsg.put("DEPARTURE_TIME","离开湖北境内日期格式不正确");
                                            }
                                        }else{
                                            repHealthyInfoWithBLOBs.setDepartureTime(null);
                                        }
                                        break;
                                    case 14:
                                        //返沪前居住地址
                                        repHealthyInfoWithBLOBs.setHomeAddress(cell.getStringCellValue());
                                        break;
                                    case 15:
                                        //返沪日期
                                        if(!StringUtils.checkNull(cell.getStringCellValue())){
                                            if (DateFormat.isLegalDate(cell.getStringCellValue())) {
                                                repHealthyInfoWithBLOBs.setReturnTime(DateFormat.getDates(cell.getStringCellValue()));
                                            }else{
                                                rowMsg.put("RETURN_TIME","返沪日期格式不正确");
                                            }
                                        }
                                        break;
                                    case 16:
                                        //拟返沪日期
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            if (DateFormat.isLegalDate(cell.getStringCellValue())) {
                                                repHealthyInfoWithBLOBs.setExpectReturnTime(DateFormat.getDates(cell.getStringCellValue()));
                                            } else {
                                                rowMsg.put("EXPECT_RETURN_TIME", "拟返沪日期 日期格式不正确");
                                            }
                                        }
                                        break;
                                    case 17:
                                        //在沪住所所属街道
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            String str = null;
                                            switch (cell.getStringCellValue()) {
                                                case "江川路街道":
                                                    str = "1";
                                                    break;
                                                case "新虹街道":
                                                    str = "2";
                                                    break;
                                                case "古美路街道":
                                                    str = "3";
                                                    break;
                                                case "浦锦街道":
                                                    str = "4";
                                                    break;
                                                case "莘庄镇":
                                                    str = "5";
                                                    break;
                                                case "七宝镇":
                                                    str = "6";
                                                    break;
                                                case "浦江镇":
                                                    str = "7";
                                                    break;
                                                case "梅陇镇":
                                                    str = "8";
                                                    break;
                                                case "虹桥镇":
                                                    str = "9";
                                                    break;
                                                case "马桥镇":
                                                    str = "10";
                                                    break;
                                                case "吴泾镇":
                                                    str = "11";
                                                    break;
                                                case "华漕镇":
                                                    str = "12";
                                                    break;
                                                case "颛桥镇":
                                                    str = "13";
                                                    break;
                                                case "莘庄工业区":
                                                    str = "14";
                                                    break;
                                                case "外区街镇":
                                                    str = "15";
                                                    break;
                                            }
                                            if(!StringUtils.checkNull(str)){
                                                repHealthyInfoWithBLOBs.setLiveStreet(str);
                                            }else{
                                                rowMsg.put("LIVE_STREET","在沪居住地所在街镇字段不正确");
                                            }
                                        }else{
                                            rowMsg.put("LIVE_STREET","在沪居住地所在街镇不能为空");

                                        }
                                        break;
                                    case 18:
                                        //在沪居住地址
                                        if (!StringUtils.checkNull(cell.getStringCellValue())){
                                            repHealthyInfoWithBLOBs.setLivePlace(cell.getStringCellValue());
                                        }else{
                                            rowMsg.put("LIVE_PLACE","在沪居住地址不能为空");
                                        }
                                        break;
                                    case 19:
                                        //本人健康状况
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            String str = null;
                                            switch (cell.getStringCellValue()) {
                                                case "正常":
                                                    str = "1";
                                                    break;
                                                case "异常":
                                                    str = "2";
                                                    break;
                                                case "异常（发烧、咳嗽、腹泻、呕吐、其他）":
                                                    str = "2";
                                                    break;
                                                case "疑似":
                                                    str = "3";
                                                    break;
                                                case "确诊":
                                                    str = "4";
                                                    break;
                                                case "密切接触未过隔离期":
                                                    str = "5";
                                                    break;
                                            }
                                            if (!StringUtils.checkNull(str)){
                                                repHealthyInfoWithBLOBs.setHealthStatus(str);
                                            }else{
                                                rowMsg.put("HEALTH_STATUS","本人健康状况字段不正确");
                                            }
                                        }else{
                                            rowMsg.put("HEALTH_STATUS","本人健康状况不能为空");
                                        }
                                        break;
                                    case 20:
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            switch (cell.getStringCellValue()) {
                                                case "否":
                                                    repHealthyInfoWithBLOBs.setTemperatureYn("0");
                                                    break;
                                                case "是":
                                                    repHealthyInfoWithBLOBs.setTemperatureYn("1");
                                                    break;
                                                default:
                                                    rowMsg.put("TEMPERATURE_YN","是否注册健康云每日体温监测字段不正确");
                                                    break;
                                            }
                                        }else{
                                            rowMsg.put("TEMPERATURE_YN","是否注册健康云每日体温监测不能为空");
                                        }
                                        break;
                                    case 21:
                                        //隔离情况
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            String str = null;
                                            switch (cell.getStringCellValue()) {
                                                case "集中隔离":
                                                    str = "1";
                                                    break;
                                                case "居家隔离":
                                                    str = "2";
                                                    break;
                                                case "健康观察":
                                                    str = "3";
                                                    break;
                                                case "解除隔离":
                                                    str = "4";
                                                    break;
                                                case "未隔离":
                                                    str = "5";
                                                    break;
                                            }
                                            if (!StringUtils.checkNull(str)){
                                                repHealthyInfoWithBLOBs.setIsolation(str);
                                            }else{
                                                rowMsg.put("ISOLATION","隔离情况字段不正确");
                                            }
                                        }else{
                                            repHealthyInfoWithBLOBs.setIsolation(null);
                                        }
                                        break;
                                    case 22:
                                        //隔离日期
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            if (DateFormat.isLegalDate(cell.getStringCellValue())) {
                                                repHealthyInfoWithBLOBs.setIsolationTime(DateFormat.getDates(cell.getStringCellValue()));
                                            } else {
                                                rowMsg.put("ISOLATION_TIME", "隔离日期 日期格式不正确");
                                            }
                                        }
                                        break;
                                    case 23:
                                        //隔离地点
                                        repHealthyInfoWithBLOBs.setIsolationPlace(cell.getStringCellValue());
                                        break;
                                    case 24:
                                        //解除隔离的日期
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            if (DateFormat.isLegalDate(cell.getStringCellValue())) {
                                                repHealthyInfoWithBLOBs.setRemIsolationTime(DateFormat.getDates(cell.getStringCellValue()));
                                            } else {
                                                rowMsg.put("REM_ISOLATION_TIME", "解除隔离的日期 格式不正确");
                                            }
                                        }
                                        break;
                                    case 25:
                                        //疑似病例确认日期
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            if (DateFormat.isLegalDate(cell.getStringCellValue())) {
                                                repHealthyInfoWithBLOBs.setEbIllTime(DateFormat.getDates(cell.getStringCellValue()));
                                            } else {
                                                rowMsg.put("EB_ILL_TIME", "疑似病例确认日期 日期格式不正确");
                                            }
                                        }
                                        break;
                                    case 26:
                                        //疑似病例 就诊医院
                                        repHealthyInfoWithBLOBs.setDiagnosis(cell.getStringCellValue());
                                        break;
                                    case 27:
                                        //疑似病例治愈（出院）日期
                                        if (!StringUtils.checkNull(cell.getStringCellValue())){
                                            if (DateFormat.isLegalDate(cell.getStringCellValue())) {
                                                repHealthyInfoWithBLOBs.setCureTime(DateFormat.getDates(cell.getStringCellValue()));
                                            }else{
                                                rowMsg.put("CURE_TIME","疑似病例解除日期 格式不正确");
                                            }
                                        }

                                        break;
                                    case 28:
                                        //确诊病例 确认日期
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            if (DateFormat.isLegalDate(cell.getStringCellValue())) {
                                                repHealthyInfoWithBLOBs.setConEbIllTime(DateFormat.getDates(cell.getStringCellValue()));
                                            } else {
                                                rowMsg.put("CON_EB_ILL_TIME", "确诊病例确认日期 日期格式不正确");
                                            }
                                        }
                                        break;
                                    case 29:
                                        //确诊病例  就诊医院
                                        repHealthyInfoWithBLOBs.setConDiagnosis(cell.getStringCellValue());
                                        break;
                                    case 30:
                                        //确诊病例  治愈日期
                                        if (!StringUtils.checkNull(cell.getStringCellValue())){
                                            if (DateFormat.isLegalDate(cell.getStringCellValue())) {
                                                repHealthyInfoWithBLOBs.setConCureTime(DateFormat.getDates(cell.getStringCellValue()));
                                            }else{
                                                rowMsg.put("CON_CURE_TIME","确诊病例出院日期 日期格式不正确");
                                            }
                                        }

                                        break;
                                    case 31:
                                        //病例基本情况(患病情况描述)
                                        repHealthyInfoWithBLOBs.setEbIllDesc(cell.getStringCellValue());
                                        break;
                                    case 32:
                                        //备注
                                        repHealthyInfoWithBLOBs.setRemark(cell.getStringCellValue());
                                        break;
                                }
                            }

                            //判断是否有异常数据
                            if (rowMsg.size()>0){
                                rowMsg.put("row",rowNum+1); //报错行数
                                falseNum++; //报错数量
                                listMsg.add(rowMsg); //报错集合
                                continue;
                            }
                            ToJson<RepHealthyInfoWithBLOBs> msg = insertHealthyInfo(request, repHealthyInfoWithBLOBs);
                            if (msg.isFlag()){
                                trueNum++;
                            }
                        }
                    }
                    mapMsg.put("trueNum",trueNum);
                    mapMsg.put("falseNum",falseNum);
                    mapMsg.put("listMsg",listMsg);

                    toJson.setObject(mapMsg);
                    toJson.setFlag(0);
                    toJson.setMsg("true");
                }else{
                    toJson.setMsg("请导入.xls或.xlsx格式的文件");
                }
            }
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.getMessage());
        }
        return toJson;
    }


    /**
     * 学生健康信息导出
     * @param request
     * @param response
     * @return
     */
    @Override
    public ToJson ExportHealthyInfo(HttpServletRequest request, HttpServletResponse response,
                                    String keyWork,
                                    String schoolManageType,
                                    String staReturnTime,
                                    String endReturnTime,
                                    String staExpectReturnTime,
                                    String endExpectReturnTime,
                                    RepHealthyInfoWithBLOBs repHealthyInfoWithBLOBs) {
        ToJson toJson=new ToJson(1,"false");
        OutputStream out = null;
        try {
            Cookie redisSessionCookie = CookiesUtil.getCookieByName(request, "redisSessionId");
            Users users = SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users(),redisSessionCookie);
            Map mapl = BeanUtils.describe(repHealthyInfoWithBLOBs);
            mapl.put("keyWork",keyWork);
            mapl.put("schoolManageType",schoolManageType);
            mapl.put("createTime",repHealthyInfoWithBLOBs.getCreateTime());
            mapl.put("returnTime",repHealthyInfoWithBLOBs.getReturnTime());
            mapl.put("expectReturnTime",repHealthyInfoWithBLOBs.getExpectReturnTime());
            Map<String, Object> deptMapById = departmentMapper.getDeptMapById(users.getDeptId());
            String organNum = deptMapById.get("ORGAN_NUM").toString();
            if(StringUtils.checkNull(organNum) ||  organNum.indexOf("3101129000" )== -1 ){
                mapl.put("deptId",users.getDeptId());
            }

            List<Map<String,Object>> mapList=repHealthyInfoMapper.findListMap(mapl);
            for (Map<String,Object> map:mapList) {
                //身份类型
                if (!StringUtils.checkNull((String)map.get("IDENTITY_TYPE"))){
                    switch ((String)map.get("IDENTITY_TYPE")){
                        case "1":
                            map.put("IDENTITY_TYPE","学生");
                            break;
                        case "2":
                            map.put("IDENTITY_TYPE","教职员工");
                            break;
                    }
                }
                //学生特有字段
                if (!StringUtils.checkNull((String)map.get("STU_GRADE"))){
                    switch ((String)map.get("STU_GRADE")){
                        case "1":
                            map.put("STU_GRADE","托育0-3");
                            break;
                        case "2":
                            map.put("STU_GRADE","幼儿园托班");
                            break;
                        case "3":
                            map.put("STU_GRADE","小班");
                            break;
                        case "4":
                            map.put("STU_GRADE","中班");
                            break;
                        case "5":
                            map.put("STU_GRADE","大班");
                            break;
                        case "6":
                            map.put("STU_GRADE","一年级");
                            break;
                        case "7":
                            map.put("STU_GRADE","二年级");
                            break;
                        case "8":
                            map.put("STU_GRADE","三年级");
                            break;
                        case "9":
                            map.put("STU_GRADE","四年级");
                            break;
                        case "10":
                            map.put("STU_GRADE","五年级");
                            break;
                        case "11":
                            map.put("STU_GRADE","六年级");
                            break;
                        case "12":
                            map.put("STU_GRADE","七年级");
                            break;
                        case "13":
                            map.put("STU_GRADE","八年级");
                            break;
                        case "14":
                            map.put("STU_GRADE","九年级");
                            break;
                        case "15":
                            map.put("STU_GRADE","十年级");
                            break;
                        case "16":
                            map.put("STU_GRADE","十一年级");
                            break;
                        case "17":
                            map.put("STU_GRADE","十二年级");
                            break;
                        case "18":
                            map.put("STU_GRADE","中职校一年级");
                            break;
                        case "19":
                            map.put("STU_GRADE","中职校二年级");
                            break;
                        case "20":
                            map.put("STU_GRADE","中职校三年级");
                            break;
                        case "21":
                            map.put("STU_GRADE","中职校四年级");
                            break;
                        case "22":
                            map.put("STU_GRADE","成教中心社区学校老年大学");
                            break;
                        case "23":
                            map.put("STU_GRADE","全日制培训机构");
                            break;
                    }
                }

                //教师特有
                if (!StringUtils.checkNull((String)map.get("TEA_POST"))){
                    switch ((String)map.get("TEA_POST")){
                        case "1":
                            map.put("TEA_POST","校级领导");
                            break;
                        case "2":
                            map.put("TEA_POST","中层干部");
                            break;
                        case "3":
                            map.put("TEA_POST","教师");
                            break;
                        case "4":
                            map.put("TEA_POST","外聘教师");
                            break;
                        case "5":
                            map.put("TEA_POST","教辅人员");
                            break;
                        case "6":
                            map.put("TEA_POST","施工人员");
                            break;
                    }
                }

                //性别
                if (!StringUtils.checkNull((String)map.get("SEX"))){
                    switch ((String)map.get("SEX")) {
                        case "2":
                            map.put("SEX", "女");
                            break;
                        case "1":
                            map.put("SEX", "男");
                            break;
                    }
                }

                //当日状态
                if (!StringUtils.checkNull((String)map.get("DAY_STATUS"))){
                    switch ((String)map.get("DAY_STATUS")) {
                        case "1":
                            map.put("DAY_STATUS", "当日在沪");
                            break;
                        case "2":
                            map.put("DAY_STATUS", "当日在重点地区（湖北武汉）");
                            break;
                        case "3":
                            map.put("DAY_STATUS", "当日在重点地区（湖北其他地区）");
                            break;
                        case "4":
                            map.put("DAY_STATUS", "当日在国内其他地区");
                            break;
                        case "5":
                            map.put("DAY_STATUS", "当日在境外（含中国港澳台地区）");
                            break;
                        case "6":
                            map.put("DAY_STATUS", "返校途中");
                            break;
                    }
                }

                //留校情况
                if (!StringUtils.checkNull((String)map.get("DETENTION"))){
                    switch ((String)map.get("DETENTION")) {
                        case "1":
                            map.put("DETENTION", "未留校");
                            break;
                        case "2":
                            map.put("DETENTION", "留校");
                            break;
                    }
                }

                //在沪住所所属街道
                if (!StringUtils.checkNull((String)map.get("LIVE_STREET"))){
                    switch ((String)map.get("LIVE_STREET")) {
                        case "1":
                            map.put("LIVE_STREET", "江川路街道");
                            break;
                        case "2":
                            map.put("LIVE_STREET", "新虹街道");
                            break;
                        case "3":
                            map.put("LIVE_STREET", "古美路街道");
                            break;
                        case "4":
                            map.put("LIVE_STREET", "浦锦街道");
                            break;
                        case "5":
                            map.put("LIVE_STREET", "莘庄镇");
                            break;
                        case "6":
                            map.put("LIVE_STREET", "七宝镇");
                            break;
                        case "7":
                            map.put("LIVE_STREET", "浦江镇");
                            break;
                        case "8":
                            map.put("LIVE_STREET", "梅陇镇");
                            break;
                        case "9":
                            map.put("LIVE_STREET", "虹桥镇");
                            break;
                        case "10":
                            map.put("LIVE_STREET", "马桥镇");
                            break;
                        case "11":
                            map.put("LIVE_STREET", "吴泾镇");
                            break;
                        case "12":
                            map.put("LIVE_STREET", "华漕镇");
                            break;
                        case "13":
                            map.put("LIVE_STREET", "颛桥镇");
                            break;
                        case "14":
                            map.put("LIVE_STREET", "莘庄工业区");
                            break;
                        case "15":
                            map.put("LIVE_STREET", "外区街镇");
                            break;
                    }
                }

                //本人健康状况
                if (!StringUtils.checkNull((String)map.get("HEALTH_STATUS"))){
                    switch ((String)map.get("HEALTH_STATUS")) {
                        case "1":
                            map.put("HEALTH_STATUS", "正常");
                            break;
                        case "2":
                            map.put("HEALTH_STATUS", "异常");
                            break;
                        case "3":
                            map.put("HEALTH_STATUS", "疑似");
                            break;
                        case "4":
                            map.put("HEALTH_STATUS", "确诊");
                            break;
                        case "5":
                            map.put("HEALTH_STATUS", "密切接触未过隔离期");
                            break;
                    }
                }

                //是否注册健康云每日体温监测
                if (!StringUtils.checkNull((String)map.get("TEMPERATURE_YN"))){
                    switch ((String)map.get("TEMPERATURE_YN")) {
                        case "0":
                            map.put("TEMPERATURE_YN", "否");
                            break;
                        case "1":
                            map.put("TEMPERATURE_YN", "是");
                            break;
                    }
                }

                //隔离情况
                if (!StringUtils.checkNull((String)map.get("ISOLATION"))){
                    switch ((String)map.get("ISOLATION")) {
                        case "1":
                            map.put("ISOLATION", "集中隔离");
                            break;
                        case "2":
                            map.put("ISOLATION", "居家隔离");
                            break;
                        case "3":
                            map.put("ISOLATION", "健康观察");
                            break;
                        case "4":
                            map.put("ISOLATION", "解除隔离");
                            break;
                        case "5":
                            map.put("ISOLATION", "未隔离");
                            break;
                    }
                }


            }

            String[] secondTitles=new String[]{
                    "学校编码",
                    "学校名称",
                    "对象类型",
                    "学生所在年级",
                    "学生所在班级",
                    "教职工职务",
                    "姓名",
                    "性别",
                    "国籍",
                    "身份证号",
                    "手机",
                    "当日状态",
                    "留校情况",
                    "离开湖北境内日期",
                    "返沪前居住地址",
                    "返沪日期",
                    "拟返沪日期",
                    "在沪居住地所在街镇",
                    "在沪居住地址",
                    "本人健康状况",
                    "是否注册健康云每日体温监测",
                    "隔离情况",
                    "隔离日期",
                    "隔离地点",
                    "解除隔离日期",
                    "“疑似病例”确认日期",
                    "“疑似病例”就诊医院",
                    "“疑似病例”解除日期",
                    "“确诊病例”确认日期",
                    "“确诊病例”就诊医院",
                    "“确诊病例”出院日期",
                    "病例基本情况",
                    "备注"};  //表头
            String[] beanProperty=new String[]{
                    "ORGAN_NUM",
                    "ORGAN_FULLNAME",
                    "IDENTITY_TYPE",
                    "STU_GRADE",
                    "STU_CLASS",
                    "TEA_POST",
                    "USER_NAME",
                    "SEX",
                    "NATIONALITY",
                    "IDENTITY_NO",
                    "PHONE",
                    "DAY_STATUS",
                    "DETENTION",
                    "DEPARTURE_TIME",
                    "HOME_ADDRESS",
                    "RETURN_TIME",
                    "EXPECT_RETURN_TIME",
                    "LIVE_STREET",
                    "LIVE_PLACE",
                    "HEALTH_STATUS",
                    "TEMPERATURE_YN",
                    "ISOLATION",
                    "ISOLATION_TIME",
                    "ISOLATION_PLACE",
                    "REM_ISOLATION_TIME",
                    "EB_ILL_TIME",
                    "DIAGNOSIS",
                    "CURE_TIME",
                    "CON_EB_ILL_TIME",
                    "CON_DIAGNOSIS",
                    "CON_CURE_TIME",
                    "EB_ILL_DESC",
                    "REMARK"
            };

            HSSFWorkbook tableWork = new HSSFWorkbook();
            HSSFWorkbook excelWork = this.makeSecondHead(tableWork,  secondTitles);
            HSSFWorkbook workbook = this.exportExcelData(excelWork, mapList, beanProperty);
            response.setContentType("text/html;charset=UTF-8");
            out = response.getOutputStream();
            String filename = FileUtils.encodeDownloadFilename("健康信息导出.xls",request.getHeader("user-agent"));
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("content-disposition","attachment;filename=" + filename);
            workbook.write(out);
            out.flush();
            out.close();
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.getMessage());
        }finally {
            try {
                if(out!=null){
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return toJson;
    }

    public <T> HSSFWorkbook exportExcelData(HSSFWorkbook workbook, List<Map<String,Object>> dataList, String[] beanPropertys) throws Exception {
        HSSFSheet sheet = workbook.getSheetAt(0);
        //样式
        HSSFCellStyle hssfCellStyle = this.HSSFStyle(workbook);

        // 填充数据
        for (int j = 0; j < dataList.size(); j++) {
            HSSFRow rowData = sheet.createRow(j + 1);
            Map<String, Object> stringObjectMap1 = dataList.get(j);
            stringObjectMap1.put("NUMBER",j+1);
            for(int k=0; k<beanPropertys.length; k++){
                Object value = stringObjectMap1.get(beanPropertys[k]);
                HSSFCell cellData = rowData.createCell(k);
                try {
                    if (value!=null && !value.equals("")) {
                        cellData.setCellValue(value.toString());
                    }else {
                        cellData.setCellValue("");

                    }
                    cellData.setCellStyle(hssfCellStyle);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return workbook;
    }

    public  HSSFWorkbook makeSecondHead(HSSFWorkbook workbook, String[] secondTitles){
        // 创建用户属性栏
        HSSFSheet sheet = workbook.createSheet("sheet1");// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet

        HSSFRow rowField = sheet.createRow(0); // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        for (int i = 0; i < secondTitles.length; i++) {
            //创建公共列头样式
            HSSFCellStyle styleField = this.createStyle(workbook, (short)13);
            HSSFCell cell = rowField.createCell(i);
            switch (i){
                case 0:
                case 1:
                case 2:
                case 6:
                case 7:
                case 8:
                case 10:
                case 11:
                case 12:
                case 17:
                case 18:
                case 19:
                case 20:
                    styleField.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
                    styleField.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    //sheet.setDefaultColumnStyle(i, styleField);
                    cell.setCellValue(secondTitles[i]);
                    cell.setCellStyle(styleField);
                    break;
                default:
                    cell.setCellValue(secondTitles[i]);
                    cell.setCellStyle(styleField);
                    break;
            }
        }
        return workbook;
    }

    public static HSSFCellStyle createStyle(HSSFWorkbook workbook, short fontSize){
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 创建一个字体样式
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints(fontSize);

        //字体加粗
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    public static HSSFCellStyle HSSFStyle(HSSFWorkbook workbook){
        HSSFCellStyle styleData = workbook.createCellStyle();
        styleData.setAlignment(HorizontalAlignment.CENTER);
        styleData.setVerticalAlignment(VerticalAlignment.CENTER);
        return styleData;
    }


    /**
     * 教育局单独导入接口
     * @param request
     * @param file
     * @return
     */
    @Override
    public ToJson ImportEducationBureau(HttpServletRequest request, MultipartFile file,String fillDate) {
        ToJson toJson=new ToJson(1,"err");
        InputStream in = null;
        try{
            Map mapMsg=new HashMap();
            List listMsg=new ArrayList();
            //返回成功失败条数
            int trueNum=0;
            int falseNum=0;
            //添加失败的信息
            Map<String ,Integer> setErr = new HashMap();
            // 判断是否为空文件
            if (file.isEmpty()) {
                toJson.setMsg("请上传文件！");
                toJson.setFlag(1);
                return toJson;
            } else {
                String fileName = file.getOriginalFilename();
                if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
                    in = file.getInputStream();
                    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(in);
                    HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
                    if (hssfSheet!=null) {
                        Set<String> namePhone=new HashSet();
                        //考虑性能进行从数据库一次查询获取到当前时间的 姓名和手机号
                        Map map=new HashMap();
                        map.put("createTime",fillDate);
                        List<RepHealthyInfoWithBLOBs> list = repHealthyInfoMapper.findHInfoNamephone(map);
                        for (RepHealthyInfoWithBLOBs bloBs: list) {
                            namePhone.add(StringUtils.checkNull(bloBs.getUserName()) ? "" : bloBs.getUserName().toUpperCase()+"-"+bloBs.getPhone());
                        }
                        //部门
                        Map<String ,Map<String, Object>> dempMap = new HashMap<>();
                        //更新
                        Set<RepHealthyInfoWithBLOBs> updateList = new HashSet<>();
                        //新增
                        Set<RepHealthyInfoWithBLOBs> insertList = new HashSet<>();
                        //从第二行开始，一行一行遍历
                        for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                            HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                            HSSFCell cell1 = hssfRow.getCell(0);
                            if(hssfRow == null){
                                break ;
                            }else if (cell1 == null){
                                break ;
                            }
                            RepHealthyInfoWithBLOBs repHealthyInfoWithBLOBs = new RepHealthyInfoWithBLOBs();
                            repHealthyInfoWithBLOBs.setCreateTime(new Date());
                            Map rowMsg =new HashMap();
                            for (int i=0;i<hssfRow.getPhysicalNumberOfCells();i++) {
                                HSSFCell cell = hssfRow.getCell(i);
                                if(cell == null){
                                    break;
                                }else{
                                    cell.setCellType(CellType.STRING);
                                }
                                switch (i){
                                    case 0:
                                        //学校编码
                                        String str1=cell.getStringCellValue();
                                        if (!StringUtils.checkNull(str1)) {
                                            repHealthyInfoWithBLOBs.setOrganNum(str1);
                                        }else{
                                            rowMsg.put("ORGAN_NUM","学校编码不能为空");
                                        }
                                        break;
                                    case 1:
                                        //学校简称
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            repHealthyInfoWithBLOBs.setSchoolName(cell.getStringCellValue());
                                        }else{
                                            rowMsg.put("SCHOOL_NAME","学校简称不能为空");
                                        }
                                        break;
                                    case 2:
                                        //身份类型 对象类型
                                        if (!StringUtils.checkNull(cell.getStringCellValue())){
                                            switch (cell.getStringCellValue()){
                                                case "学生":
                                                    repHealthyInfoWithBLOBs.setIdentityType("1");
                                                    break;
                                                case "教职员工":
                                                    repHealthyInfoWithBLOBs.setIdentityType("2");
                                                    break;
                                                default:
                                                    rowMsg.put("IDENTITY_TYPE","对象类型字段不正确");
                                                    break;
                                            }
                                        }else{
                                            rowMsg.put("IDENTITY_TYPE","对象类型不能为空");
                                        }
                                        break;
                                    case 3:
                                        //学生特有字段 学生所在年级
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            String str = null;
                                            switch (cell.getStringCellValue()) {
                                                case "托育0-3":
                                                    str = "1";
                                                    break;
                                                case "幼儿园托班":
                                                    str = "2";
                                                    break;
                                                case "小班":
                                                    str = "3";
                                                    break;
                                                case "中班":
                                                    str = "4";
                                                    break;
                                                case "大班":
                                                    str = "5";
                                                    break;
                                                case "一年级":
                                                    str = "6";
                                                    break;
                                                case "二年级":
                                                    str = "7";
                                                    break;
                                                case "三年级":
                                                    str = "8";
                                                    break;
                                                case "四年级":
                                                    str = "9";
                                                    break;
                                                case "五年级":
                                                    str = "10";
                                                    break;
                                                case "六年级":
                                                    str = "11";
                                                    break;
                                                case "七年级":
                                                    str = "12";
                                                    break;
                                                case "八年级":
                                                    str = "13";
                                                    break;
                                                case "九年级":
                                                    str = "14";
                                                    break;
                                                case "十年级":
                                                    str = "15";
                                                    break;
                                                case "十一年级":
                                                    str = "16";
                                                    break;
                                                case "十二年级":
                                                    str = "17";
                                                    break;
                                                case "中职校一年级":
                                                    str = "18";
                                                    break;
                                                case "中职校二年级":
                                                    str = "19";
                                                    break;
                                                case "中职校三年级":
                                                    str = "20";
                                                    break;
                                                case "中职校四年级":
                                                    str = "21";
                                                    break;
                                                case "成教中心社区学校老年大学":
                                                    str = "22";
                                                    break;
                                                case "全日制培训机构":
                                                    str = "23";
                                                    break;
                                            }
                                            if (str!=null && !str.equals("")){
                                                repHealthyInfoWithBLOBs.setStuGrade(str);
                                            }else{
                                                rowMsg.put("STU_GRADE","学生所在年级字段不正确");
                                            }
                                        }else{
                                            repHealthyInfoWithBLOBs.setStuGrade("");
                                        }
                                        break;
                                    case 4:
                                        //学生特有字段 学生所在班级
                                        repHealthyInfoWithBLOBs.setStuClass(cell.getStringCellValue());
                                        break;
                                    case 5:
                                        //教师特有 教职工职务
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            String str = null;
                                            switch (cell.getStringCellValue()) {
                                                case "校级领导":
                                                    str = "1";
                                                    break;
                                                case "中层干部":
                                                    str = "2";
                                                    break;
                                                case "教师":
                                                    str = "3";
                                                    break;
                                                case "外聘教师":
                                                    str = "4";
                                                    break;
                                                case "教辅人员":
                                                    str = "5";
                                                    break;
                                                case "施工人员":
                                                    str = "6";
                                                    break;
                                            }
                                            if(!StringUtils.checkNull(str)){
                                                repHealthyInfoWithBLOBs.setTeaPost(str);
                                            }else{
                                                rowMsg.put("TEA_POST","教职工职务字段不正确");

                                            }
                                        }else{
                                            repHealthyInfoWithBLOBs.setTeaPost("");
                                        }
                                        break;
                                    case 6:
                                        //姓名
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            repHealthyInfoWithBLOBs.setUserName(cell.getStringCellValue());
                                        }else{
                                            rowMsg.put("USER_NAME","姓名不能为空");
                                        }
                                        break;
                                    case 7:
                                        //性别
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {

                                            switch (cell.getStringCellValue()) {
                                                case "女":
                                                    repHealthyInfoWithBLOBs.setSex("2");
                                                    break;
                                                case "男":
                                                    repHealthyInfoWithBLOBs.setSex("1");
                                                    break;
                                                default:
                                                    rowMsg.put("SEX","性别字段不正确");
                                                    break;
                                            }
                                        }else{
                                            rowMsg.put("SEX","性别不能为空");
                                        }
                                        break;
                                    case 8:
                                        //国籍
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            repHealthyInfoWithBLOBs.setNationality(cell.getStringCellValue());
                                        }else{
                                            rowMsg.put("NATIONALITY","国籍不能为空");
                                        }
                                        break;
                                    case 9:
                                        //身份证号
                                        repHealthyInfoWithBLOBs.setIdentityNo(cell.getStringCellValue());
                                        break;
                                    case 10:
                                        //联系电话
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            repHealthyInfoWithBLOBs.setPhone(cell.getStringCellValue());
                                        }else{
                                            rowMsg.put("PHONE","手机不能为空");
                                        }
                                        break;
                                    case 11:
                                        //当日状态
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            String str = null;
                                            switch (cell.getStringCellValue()) {
                                                case "当日在沪":
                                                    str = "1";
                                                    break;
                                                case "当日在重点地区（湖北武汉）":
                                                    str = "2";
                                                    break;
                                                case "当日在重点地区（湖北其他地区）":
                                                    str = "3";
                                                    break;
                                                case "当日在国内其他地区":
                                                    str = "4";
                                                    break;
                                                case "当日在境外（含中国港澳台地区）":
                                                    str = "5";
                                                    break;
                                                case "返校途中":
                                                    str = "6";
                                                    break;
                                            }
                                            if(!StringUtils.checkNull(str)) {
                                                repHealthyInfoWithBLOBs.setDayStatus(str);
                                            }else{
                                                rowMsg.put("DAY_STATUS","当日状态字段不正确");
                                            }
                                        }else{
                                            rowMsg.put("DAY_STATUS","当日状态不能为空");
                                        }
                                        break;
                                    case 12:
                                        //留校情况
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            switch (cell.getStringCellValue()) {
                                                case "未留校":
                                                    repHealthyInfoWithBLOBs.setDetention("1");
                                                    break;
                                                case "留校":
                                                    repHealthyInfoWithBLOBs.setDetention("2");
                                                    break;
                                                default:
                                                    rowMsg.put("DETENTION","留校情况字段不正确");
                                                    break;
                                            }
                                        }else{
                                            rowMsg.put("DETENTION","留校情况不能为空");
                                        }
                                        break;
                                    case 13:
                                        // 离开湖北境内日期 允许为空
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            if (DateFormat.isLegalDate(cell.getStringCellValue())){
                                                repHealthyInfoWithBLOBs.setDepartureTime(DateFormat.getDates(cell.getStringCellValue()));
                                            }else{
                                                rowMsg.put("DEPARTURE_TIME","离开湖北境内日期格式不正确");
                                            }
                                        }else{
                                            repHealthyInfoWithBLOBs.setDepartureTime(null);
                                        }
                                        break;
                                    case 14:
                                        //返沪前居住地址
                                        repHealthyInfoWithBLOBs.setHomeAddress(cell.getStringCellValue());
                                        break;
                                    case 15:
                                        //返沪日期
                                        if(!StringUtils.checkNull(cell.getStringCellValue())){
                                            if (DateFormat.isLegalDate(cell.getStringCellValue())) {
                                                repHealthyInfoWithBLOBs.setReturnTime(DateFormat.getDates(cell.getStringCellValue()));
                                            }else{
                                                rowMsg.put("RETURN_TIME","返沪日期格式不正确");
                                            }
                                        }
                                        break;
                                    case 16:
                                        //拟返沪日期
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            if (DateFormat.isLegalDate(cell.getStringCellValue())) {
                                                repHealthyInfoWithBLOBs.setExpectReturnTime(DateFormat.getDates(cell.getStringCellValue()));
                                            } else {
                                                rowMsg.put("EXPECT_RETURN_TIME", "拟返沪日期 日期格式不正确");
                                            }
                                        }
                                        break;
                                    case 17:
                                        //在沪住所所属街道
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            String str = null;
                                            switch (cell.getStringCellValue()) {
                                                case "江川路街道":
                                                    str = "1";
                                                    break;
                                                case "新虹街道":
                                                    str = "2";
                                                    break;
                                                case "古美路街道":
                                                    str = "3";
                                                    break;
                                                case "浦锦街道":
                                                    str = "4";
                                                    break;
                                                case "莘庄镇":
                                                    str = "5";
                                                    break;
                                                case "七宝镇":
                                                    str = "6";
                                                    break;
                                                case "浦江镇":
                                                    str = "7";
                                                    break;
                                                case "梅陇镇":
                                                    str = "8";
                                                    break;
                                                case "虹桥镇":
                                                    str = "9";
                                                    break;
                                                case "马桥镇":
                                                    str = "10";
                                                    break;
                                                case "吴泾镇":
                                                    str = "11";
                                                    break;
                                                case "华漕镇":
                                                    str = "12";
                                                    break;
                                                case "颛桥镇":
                                                    str = "13";
                                                    break;
                                                case "莘庄工业区":
                                                    str = "14";
                                                    break;
                                                case "外区街镇":
                                                    str = "15";
                                                    break;
                                            }
                                            if(!StringUtils.checkNull(str)){
                                                repHealthyInfoWithBLOBs.setLiveStreet(str);
                                            }else{
                                                rowMsg.put("LIVE_STREET","在沪居住地所在街镇字段不正确");
                                            }
                                        }else{
                                            rowMsg.put("LIVE_STREET","在沪居住地所在街镇不能为空");

                                        }
                                        break;
                                    case 18:
                                        //在沪居住地址
                                        if (!StringUtils.checkNull(cell.getStringCellValue())){
                                            repHealthyInfoWithBLOBs.setLivePlace(cell.getStringCellValue());
                                        }else{
                                            rowMsg.put("LIVE_PLACE","在沪居住地址不能为空");
                                        }
                                        break;
                                    case 19:
                                        //本人健康状况
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            String str = null;
                                            switch (cell.getStringCellValue()) {
                                                case "正常":
                                                    str = "1";
                                                    break;
                                                case "异常":
                                                    str = "2";
                                                    break;
                                                case "异常（发烧、咳嗽、腹泻、呕吐、其他）":
                                                    str = "2";
                                                    break;
                                                case "疑似":
                                                    str = "3";
                                                    break;
                                                case "确诊":
                                                    str = "4";
                                                    break;
                                                case "密切接触未过隔离期":
                                                    str = "5";
                                                    break;
                                            }
                                            if (!StringUtils.checkNull(str)){
                                                repHealthyInfoWithBLOBs.setHealthStatus(str);
                                            }else{
                                                rowMsg.put("HEALTH_STATUS","本人健康状况字段不正确");
                                            }
                                        }else{
                                            rowMsg.put("HEALTH_STATUS","本人健康状况不能为空");
                                        }
                                        break;
                                    case 20:
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            switch (cell.getStringCellValue()) {
                                                case "否":
                                                    repHealthyInfoWithBLOBs.setTemperatureYn("0");
                                                    break;
                                                case "是":
                                                    repHealthyInfoWithBLOBs.setTemperatureYn("1");
                                                    break;
                                                default:
                                                    rowMsg.put("TEMPERATURE_YN","是否注册健康云每日体温监测字段不正确");
                                                    break;
                                            }
                                        }else{
                                            rowMsg.put("TEMPERATURE_YN","是否注册健康云每日体温监测不能为空");
                                        }
                                        break;
                                    case 21:
                                        //隔离情况
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            String str = null;
                                            switch (cell.getStringCellValue()) {
                                                case "集中隔离":
                                                    str = "1";
                                                    break;
                                                case "居家隔离":
                                                    str = "2";
                                                    break;
                                                case "健康观察":
                                                    str = "3";
                                                    break;
                                                case "解除隔离":
                                                    str = "4";
                                                    break;
                                                case "未隔离":
                                                    str = "5";
                                                    break;
                                            }
                                            if (!StringUtils.checkNull(str)){
                                                repHealthyInfoWithBLOBs.setIsolation(str);
                                            }else{
                                                rowMsg.put("ISOLATION","隔离情况字段不正确");
                                            }
                                        }else{
                                            repHealthyInfoWithBLOBs.setIsolation(null);
                                        }
                                        break;
                                    case 22:
                                        //隔离日期
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            if (DateFormat.isLegalDate(cell.getStringCellValue())) {
                                                repHealthyInfoWithBLOBs.setIsolationTime(DateFormat.getDates(cell.getStringCellValue()));
                                            } else {
                                                rowMsg.put("ISOLATION_TIME", "隔离日期 日期格式不正确");
                                            }
                                        }
                                        break;
                                    case 23:
                                        //隔离地点
                                        repHealthyInfoWithBLOBs.setIsolationPlace(cell.getStringCellValue());
                                        break;
                                    case 24:
                                        //解除隔离的日期
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            if (DateFormat.isLegalDate(cell.getStringCellValue())) {
                                                repHealthyInfoWithBLOBs.setRemIsolationTime(DateFormat.getDates(cell.getStringCellValue()));
                                            } else {
                                                rowMsg.put("REM_ISOLATION_TIME", "解除隔离的日期 格式不正确");
                                            }
                                        }
                                        break;
                                    case 25:
                                        //疑似病例确认日期
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            if (DateFormat.isLegalDate(cell.getStringCellValue())) {
                                                repHealthyInfoWithBLOBs.setEbIllTime(DateFormat.getDates(cell.getStringCellValue()));
                                            } else {
                                                rowMsg.put("EB_ILL_TIME", "疑似病例确认日期 日期格式不正确");
                                            }
                                        }
                                        break;
                                    case 26:
                                        //疑似病例 就诊医院
                                        repHealthyInfoWithBLOBs.setDiagnosis(cell.getStringCellValue());
                                        break;
                                    case 27:
                                        //疑似病例治愈（出院）日期
                                        if (!StringUtils.checkNull(cell.getStringCellValue())){
                                            if (DateFormat.isLegalDate(cell.getStringCellValue())) {
                                                repHealthyInfoWithBLOBs.setCureTime(DateFormat.getDates(cell.getStringCellValue()));
                                            }else{
                                                rowMsg.put("CURE_TIME","疑似病例解除日期 格式不正确");
                                            }
                                        }

                                        break;
                                    case 28:
                                        //确诊病例 确认日期
                                        if (!StringUtils.checkNull(cell.getStringCellValue())) {
                                            if (DateFormat.isLegalDate(cell.getStringCellValue())) {
                                                repHealthyInfoWithBLOBs.setConEbIllTime(DateFormat.getDates(cell.getStringCellValue()));
                                            } else {
                                                rowMsg.put("CON_EB_ILL_TIME", "确诊病例确认日期 日期格式不正确");
                                            }
                                        }
                                        break;
                                    case 29:
                                        //确诊病例  就诊医院
                                        repHealthyInfoWithBLOBs.setConDiagnosis(cell.getStringCellValue());
                                        break;
                                    case 30:
                                        //确诊病例  治愈日期
                                        if (!StringUtils.checkNull(cell.getStringCellValue())){
                                            if (DateFormat.isLegalDate(cell.getStringCellValue())) {
                                                repHealthyInfoWithBLOBs.setConCureTime(DateFormat.getDates(cell.getStringCellValue()));
                                            }else{
                                                rowMsg.put("CON_CURE_TIME","确诊病例出院日期 日期格式不正确");
                                            }
                                        }
                                        break;
                                    case 31:
                                        //病例基本情况(患病情况描述)
                                        repHealthyInfoWithBLOBs.setEbIllDesc(cell.getStringCellValue());
                                        break;
                                    case 32:
                                        //备注
                                        repHealthyInfoWithBLOBs.setRemark(cell.getStringCellValue());
                                        break;
                                }
                            }

                            //判断是否有异常数据
                            if (rowMsg.size()>0){
                                rowMsg.put("row",rowNum+1); //报错行数
                                falseNum++; //报错数量
                                listMsg.add(rowMsg); //报错集合
                                continue;
                            }

                            repHealthyInfoWithBLOBs.setCreateTime(DateFormat.DateToStr(fillDate));
                            //判断对象是否存在
                            out:if(namePhone.contains(StringUtils.checkNull(repHealthyInfoWithBLOBs.getUserName()) ? "" : repHealthyInfoWithBLOBs.getUserName().toUpperCase() +"-"+repHealthyInfoWithBLOBs.getPhone())){
                                repHealthyInfoWithBLOBs.setUserId(null);
                                repHealthyInfoWithBLOBs.setDeptId(null);
                                repHealthyInfoWithBLOBs.setOrganNum(null);
                                if(!updateList.contains(repHealthyInfoWithBLOBs)){
                                    int i = repHealthyInfoMapper.updateNamePhone(repHealthyInfoWithBLOBs);
                                    trueNum += i;
                                    updateList.add(repHealthyInfoWithBLOBs);
                                }
                            }else{
                                //获取学校编码
                                Map map1=new HashMap();
                                map1.put("ORGAN_NUM",repHealthyInfoWithBLOBs.getOrganNum());
                                //查找部门
                                Map<String, Object> map2;
                                Map<String, Object> objectMap = dempMap.get(repHealthyInfoWithBLOBs.getOrganNum());
                                if(objectMap != null){
                                    map2 = objectMap;
                                }else{
                                    Map<String, Object> objectMap1 = repHealthyInfoMapper.finddeptNum(map1);
                                    if(objectMap1 != null){
                                        dempMap.put(repHealthyInfoWithBLOBs.getOrganNum(),objectMap1);
                                        map2 = objectMap1;
                                    }else{
                                        String str = "<"+repHealthyInfoWithBLOBs.getSchoolName() +">("+ repHealthyInfoWithBLOBs.getOrganNum()+")学校不存在";
                                        Integer integer = setErr.get(str);
                                        if(integer != null ){
                                            setErr.put(str,++integer);
                                        }else{
                                            setErr.put(str,1);
                                        }
                                        break out;
                                    }
                                }
                                if (!StringUtils.checkNull(map2.get("DEPT_ID").toString())){
                                    repHealthyInfoWithBLOBs.setDeptId(Integer.valueOf(map2.get("DEPT_ID").toString()));
                                    repHealthyInfoWithBLOBs.setUserId((String) map2.get("USER_ID"));
                                }
                                String str1=repHealthyInfoWithBLOBs.getOrganNum();
                                if (str1.length()!=4){
                                    String substring = str1.substring(str1.length() - 4);
                                    repHealthyInfoWithBLOBs.setOrganNum(substring);
                                }
                                boolean idAdd = insertList.add(repHealthyInfoWithBLOBs);
                                if(!idAdd){
                                    falseNum++;
                                    rowMsg.put("row",rowNum+1); //报错行数
                                    rowMsg.put("UserName",repHealthyInfoWithBLOBs.getUserName()+"-->用户重复"); //报错行数
                                    listMsg.add(rowMsg);
                                }

                            }
                        }
                        List<List<RepHealthyInfoWithBLOBs>> lists = fixedGrouping(new ArrayList<>(insertList), 5000);
                        if(lists != null){
                            for(List<RepHealthyInfoWithBLOBs> list1 :lists){
                                if(list1 != null && list1.size() >0){
                                    int i = repHealthyInfoMapper.insertAll(list1);
                                    trueNum += i;
                                }
                            }
                        }
                    }
                    mapMsg.put("trueNum",trueNum);
                    mapMsg.put("falseNum",falseNum);
                    mapMsg.put("listMsg",listMsg);
                    mapMsg.put("schoolErr",setErr);
                    toJson.setObject(mapMsg);
                    toJson.setFlag(0);
                    toJson.setMsg("true");
                }else{
                    toJson.setMsg("请导入.xls或.xlsx格式的文件");
                }
            }
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.getMessage());
        }finally {
            if(in != null){
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return toJson;
    }

    //list拆分
    public static <T> List<List<T>> fixedGrouping(List<T> source, int n) {
        if (null == source || source.size() == 0 || n <= 0)
            return null;
        List<List<T>> result = new ArrayList<List<T>>();
        int sourceSize = source.size();
        int size = (source.size() / n) + 1;
        for (int i = 0; i < size; i++) {
            List<T> subset = new ArrayList<T>();
            for (int j = i * n; j < (i + 1) * n; j++) {
                if (j < sourceSize) {
                    subset.add(source.get(j));
                }
            }
            result.add(subset);
        }
        return result;
    }

    /**
     * 做出判断当前登陆人是否有权限显示按钮
     * @param request
     * @return
     */
    @Override
    public ToJson isEducationBureau(HttpServletRequest request) {
        ToJson toJson=new ToJson(1,"false");
        try {
            Cookie redisSessionCookie = CookiesUtil.getCookieByName(request, "redisSessionId");
            Users users = SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users(),redisSessionCookie);
            toJson.setMsg("true");
            toJson.setFlag(0);
            toJson.setObject(users.getUserPriv());
        }catch (Exception e){
            e.printStackTrace();
        }
        return toJson;
    }
    /**
     * 师生返沪情况
     * 在沪人数: 该填报日期数据中【总人数-失联人数-当日在重点地区（湖北）人数-当日在其他地区人数-返校途中】
     * @param request
     * @param time
     * @return
     */
    @Override
    public ToJson BacktoShanghai(HttpServletRequest request, String time) {
        ToJson toJson=new ToJson(1,"false");
        try {
            Map map=new HashMap();
            map.put("createTime",time);



            //统计部门的总人数； STUDENT_NUM 学生总数,TEACHER_NUM  教师总数,STUDENT_LOST_NUM 失联学生总数,TEACHER_LOST_NUM 失联教师总数
            Map<String,Object> map1=repHealthyInfoMapper.findDeptNum();
            int STUDENT_NUM=map1.containsKey("STUDENT_NUM")?Integer.parseInt(map1.get("STUDENT_NUM").toString()):0;
            int TEACHER_NUM=map1.containsKey("TEACHER_NUM")?Integer.parseInt(map1.get("TEACHER_NUM").toString()):0;
            int STUDENT_LOST_NUM=map1.containsKey("STUDENT_LOST_NUM")?Integer.parseInt(map1.get("STUDENT_LOST_NUM").toString()):0;
            int TEACHER_LOST_NUM=map1.containsKey("TEACHER_LOST_NUM")?Integer.parseInt(map1.get("TEACHER_LOST_NUM").toString()):0;

            //统计师生 focus 2当日在重点地区, other 3当日在其他区域,  enroute 4返校途中 人数统计 下标0是学生 下标1是教师
            List<Map<String, Object>> map2=repHealthyInfoMapper.fundsumShanghai(map);
            //学生
            int sfocus = map2.get(0).containsKey("focus") ? Integer.parseInt(map2.get(0).get("focus").toString()) : 0;
            int sother = map2.get(0).containsKey("other") ? Integer.parseInt(map2.get(0).get("other").toString()) : 0;
            int senroute = map2.get(0).containsKey("enroute") ? Integer.parseInt(map2.get(0).get("enroute").toString()) : 0;

            //教师
            int tfocus = map2.get(1).containsKey("focus") ? Integer.parseInt(map2.get(1).get("focus").toString()) : 0;
            int tother = map2.get(1).containsKey("other") ? Integer.parseInt(map2.get(1).get("other").toString()) : 0;
            int tenroute = map2.get(1).containsKey("enroute") ? Integer.parseInt(map2.get(1).get("enroute").toString()) : 0;

            //学生返沪人数
            int student=STUDENT_NUM-STUDENT_LOST_NUM-sfocus-sother-senroute;
            //教师返沪人数
            int teacher=TEACHER_NUM-TEACHER_LOST_NUM-tfocus-tother-tenroute;
            map.put("student",student);
            map.put("teacher",teacher);
            map.put("WaitStudent",STUDENT_NUM-student);
            map.put("WaitTeacher",TEACHER_NUM-teacher);
            toJson.setObject(map);
            toJson.setMsg("true");
            toJson.setFlag(0);
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.getMessage());
        }
        return toJson;
    }

    @Override
    public ToJson liveanaly(HttpServletRequest request, String time) {
        ToJson toJson=new ToJson(1,"false");
        try {
            Map map=new HashMap();
            map.put("createTime",time);
            List<Map<String, Object>> findliveanalysis = repHealthyInfoMapper.findliveanaly(map);
            for (Map map1:findliveanalysis) {
                String live_street =(String) map1.get("LIVE_STREET");
                switch (live_street) {
                    case "1":
                        map1.put("LIVE_STREET", "江川路街道");
                        break;
                    case "2":
                        map1.put("LIVE_STREET", "新虹街道");
                        break;
                    case "3":
                        map1.put("LIVE_STREET", "古美路街道");
                        break;
                    case "4":
                        map1.put("LIVE_STREET", "浦锦街道");
                        break;
                    case "5":
                        map1.put("LIVE_STREET", "莘庄镇");
                        break;
                    case "6":
                        map1.put("LIVE_STREET", "七宝镇");
                        break;
                    case "7":
                        map1.put("LIVE_STREET", "浦江镇");
                        break;
                    case "8":
                        map1.put("LIVE_STREET", "梅陇镇");
                        break;
                    case "9":
                        map1.put("LIVE_STREET", "虹桥镇");
                        break;
                    case "10":
                        map1.put("LIVE_STREET", "马桥镇");
                        break;
                    case "11":
                        map1.put("LIVE_STREET", "吴泾镇");
                        break;
                    case "12":
                        map1.put("LIVE_STREET", "华漕镇");
                        break;
                    case "13":
                        map1.put("LIVE_STREET", "颛桥镇");
                        break;
                    case "14":
                        map1.put("LIVE_STREET", "莘庄工业区");
                        break;
                    case "15":
                        map1.put("LIVE_STREET", "外区街镇");
                        break;
                }
            }
            toJson.setMsg("true");
            toJson.setObj(findliveanalysis);
            toJson.setFlag(0);
        }catch (Exception e){
            toJson.setMsg(e.getMessage());
        }
        return toJson;
    }

    /**
     * 师生健康管理
     * @param request
     * @param time
     * @return
     */
    @Override
    public ToJson HealthManagement(HttpServletRequest request, String time) {
        ToJson toJson=new ToJson(1,"false");
        try {
            Map map=new HashMap();
            map.put("createTime",time);
            //在沪人员 目前居家隔离人数:该填报日期数据中【当日状态为1：当日在沪】 并且 【隔离状态为 2：居家隔离】
            Map<String, Object> findhealth = repHealthyInfoMapper.findhealth(map);

           //总数
            Map<String,Object> map1=repHealthyInfoMapper.findDeptNum();
            int STUDENT_NUM=map1.containsKey("STUDENT_NUM")?Integer.parseInt(map1.get("STUDENT_NUM").toString()):0;
            int TEACHER_NUM=map1.containsKey("TEACHER_NUM")?Integer.parseInt(map1.get("TEACHER_NUM").toString()):0;
            int total=STUDENT_NUM+TEACHER_NUM; //各个学校的 学生总数+教师总数


            // 创建一个数值格式化对象
            NumberFormat numberFormat = NumberFormat.getInstance();
            // 设置精确到小数点后2位
            numberFormat.setMaximumFractionDigits(2);

            //居家隔离
            int HomeIsolation=Integer.parseInt(findhealth.get("HomeIsolation").toString());
            String result = numberFormat.format((float) HomeIsolation / (float) total * 100);
            map.put("HomeIsolation",result);

            //健康观测
            int HealthWatch=Integer.parseInt(findhealth.get("HealthWatch").toString());
            String result2 = numberFormat.format((float) HealthWatch / (float) total * 100);
            map.put("HealthWatch",result2);

            //是否注册健康云每日体温监测
            int registered=Integer.parseInt(findhealth.get("registered").toString());
            String result3 = numberFormat.format((float) registered / (float) total * 100);
            map.put("registered",result3);

            toJson.setObject(map);
            toJson.setMsg("true");
            toJson.setFlag(0);
        }catch (Exception e){
            toJson.setMsg(e.getMessage());
        }
        return toJson;
    }


    /**
     * 师生来源地分析
     * @param request
     * @param time
     * @return
     */
    @Override
    public ToJson AnalysisOfOrigin(HttpServletRequest request, String time) {
        ToJson toJson=new ToJson(1,"false");
        try {
            Map mapres=new HashMap();
            Map map=new HashMap();

            //重点地区待返沪学生   重点地区待返沪教师    其他地区待返沪学生   其他地区待返沪教师
            //【当日状态为2：当日在重点地区（湖北）】
            //重点地区待返沪教师
            map.put("createTime",time);
            map.put("identityType",2);
            map.put("dayStatus",new Integer[]{2,3}); //当日状态
            Map<String, Object> origin = repHealthyInfoMapper.findOrigin(map);
            mapres.put("重点地区待返沪教师",origin.get("people"));

            //【当日状态为2：当日在重点地区（湖北）】
            //重点地区待返沪教师
            map.put("createTime",time);
            map.put("identityType",1);
            map.put("dayStatus",new Integer[]{2,3}); //当日状态
            Map<String, Object> origin1 = repHealthyInfoMapper.findOrigin(map);
            mapres.put("重点地区待返沪学生",origin1.get("people"));

            //【当日状态为3：当日在其他区域】
            //重点地区待返沪教师
            map.put("createTime",time);
            map.put("identityType",2);
            map.put("dayStatus",new Integer[]{4,5}); //当日状态
            Map<String, Object> origin2 = repHealthyInfoMapper.findOrigin(map);
            mapres.put("其他地区待返沪教师",origin2.get("people"));

            //【当日状态为3：当日在其他区域】
            //重点地区待返沪教师
            map.put("createTime",time);
            map.put("identityType",1);
            map.put("dayStatus",new Integer[]{4,5}); //当日状态
            Map<String, Object> origin3 = repHealthyInfoMapper.findOrigin(map);
            mapres.put("其他地区待返沪学生",origin3.get("people"));

            toJson.setObject(mapres);
            toJson.setMsg("true");
            toJson.setFlag(0);
        }catch (Exception e){
            toJson.setMsg(e.getMessage());
        }
        return toJson;
    }

    /**
     * 导入人数
     * @param request
     * @param response
     * @param file
     * @return
     */
    @Override
    public ToJson ImportNumDept(HttpServletRequest request, HttpServletResponse response, MultipartFile file) {
        ToJson toJson=new ToJson(1,"false");
        try {
            // 判断是否为空文件
            if (file.isEmpty()) {
                toJson.setMsg("请上传文件！");
                toJson.setFlag(1);
                return toJson;
            } else {
                //返回成功条数
                int trueNum=0;
                int falseNum=0;
                int repeat=0;
                List listMsg=new ArrayList();

                String fileName = file.getOriginalFilename();
                if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
                    InputStream in = file.getInputStream();
                    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(in);
                    HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
                    if (hssfSheet != null) {
                        //要修改的信息
                        Map map=new HashMap();

                        Set<String> sett= new HashSet<String>();
                        //从第二行开始，一行一行遍历
                        for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                            Map mapmsg=new HashMap();


                            HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                            HSSFCell cell = hssfRow.getCell(7);  //上报状态
                            HSSFCell cell2 = hssfRow.getCell(0);  //学校编码，
                            cell.setCellType(CellType.STRING); //统一单元格类型
                            String str=cell2.getStringCellValue();
                            if(!sett.add(str)){
                                repeat++;
                                continue;
                            }
                            if (!StringUtils.checkNull(cell.getStringCellValue()) && cell.getStringCellValue().equals("已上报")){
                                map.put("ORGAN_NUM",hssfRow.getCell(0).getStringCellValue()); //机构编码
                                map.put("ORGAN_FULLNAME",hssfRow.getCell(1).getStringCellValue()); //机构全称
                                map.put("STUDENT_NUM",hssfRow.getCell(3).getStringCellValue()); // 学生人数
                                map.put("TEACHER_NUM",hssfRow.getCell(4).getStringCellValue()); // 教职工人数
                                map.put("STUDENT_LOST_NUM",hssfRow.getCell(5).getStringCellValue()); //失联学生人数
                                map.put("TEACHER_LOST_NUM",hssfRow.getCell(6).getStringCellValue()); //失联教职工人数
                               //上报成功进行修改
                                int i=repHealthyInfoMapper.updateDept(map);
                                if (i>0){
                                    trueNum++;
                                }else {
                                    falseNum++;
                                    mapmsg.put("row",rowNum+1);
                                }
                            }else{
                                falseNum++;
                                mapmsg.put("row",rowNum+1);
                            }
                            if (mapmsg.size()>0) {
                                listMsg.add(mapmsg);
                            }
                        }
                    }
                }
                Map resMap=new HashMap();
                resMap.put("trueNum",trueNum);
                resMap.put("falseNum",falseNum);
                resMap.put("listMsg",listMsg);
                resMap.put("repeat",repeat);

                toJson.setMsg("true");
                toJson.setObject(resMap);
                toJson.setFlag(0);
            }
        }catch (Exception e){
            toJson.setMsg(e.getMessage());
        }
        return toJson;
    }


    public static String repSchoolType(String str){
        if(StringUtils.checkNull(str)){
            return "无";
        }
        switch (str.trim()){
            case "1":
                return "机关";
            case "2":
                return "直属单位";
            case "3":
                return "托育机构";
            case "4":
                return "幼儿园";
            case "5":
                return "小学";
            case "6":
                return "初级中学";
            case "7":
                return "九年一贯制";
            case "8":
                return "高级中学";
            case "9":
                return "完全中学";
            case "10":
                return "十二年一贯制";
            case "11":
                return "听力障碍学校";
            case "12":
                return "智力障碍学校";
            case "13":
                return "工读学校";
            case "14":
                return "孤残学校";
            case "15":
                return "外籍学校";
            case "16":
                return "中等职业学校";
            case "17":
                return "职业高中学校";
            case "18":
                return "开放大学";
            case "19":
                return "农村成人文化技术培训学校(机构)";
            case "20":
                return "职工技术培训学校(机构)";
            case "21":
                return "民办非学历教育机构";
            default:
                return "无";
        }
    }

    public static String repSchoolManageType(String str){
        if(StringUtils.checkNull(str)){
            return "无";
        }
        switch (str.trim()){
            case "1":
                return "机关与直属单位";
            case "2":
                return "托育机构";
            case "3":
                return "公办幼儿园";
            case "4":
                return "民办幼儿园";
            case "5":
                return "民办三级幼儿园";
            case "6":
                return "外籍幼儿园";
            case "7":
                return "公办小学";
            case "8":
                return "公办中学";
            case "9":
                return "随迁学校";
            case "10":
                return "民办中小学";
            case "11":
                return "外籍中小学";
            case "12":
                return "区管中职校";
            case "13":
                return "行业中职校";
            case "14":
                return "市属中职校";
            case "15":
                return "成教中心社区学校老年大学";
            case "16":
                return "全日制培训机构";
            default:
                return "无";
        }
    }

    public static String repStatePrivate(String str){
        if(StringUtils.checkNull(str)){
            return "无";
        }
        switch (str.trim()){
            case "1":
                return "公办";
            case "2":
                return "民办";
            default:
                return "无";
        }
    }

    public static String repLiveStreet(String str){
        if(StringUtils.checkNull(str)){
            return "无";
        }
        switch (str.trim()){
            case "1":
                return "江川路街道";
            case "2":
                return "新虹街道";
            case "3":
                return "古美路街道";
            case "4":
                return "浦锦街道";
            case "5":
                return "莘庄镇";
            case "6":
                return "七宝镇";
            case "7":
                return "浦江镇";
            case "8":
                return "梅陇镇";
            case "9":
                return "虹桥镇";
            case "10":
                return "马桥镇";
            case "11":
                return "吴泾镇";
            case "12":
                return "华漕镇";
            case "13":
                return "颛桥镇";
            case "14":
                return "莘庄工业区";
            case "15":
                return "外区街镇";
            default:
                return "无";
        }
    }

    //取出用户所属的学校
    private Map<String, String> getFatherDept(int deptParent) {
        Map<String, String> fatherDept = repHealthyInfoMapper.getFatherDept(deptParent);
        if ("0".equals(fatherDept.get("SCHOOL_PARENT")) || "99999999".equals(fatherDept.get("SCHOOL_PARENT")) ) {
            return fatherDept;
        }
        if(!StringUtils.checkNull(fatherDept.get("SCHOOL_TYPE")))
            return fatherDept;
        return getFatherDept(Integer.valueOf(fatherDept.get("SCHOOL_PARENT")));
    }
}
